%load_ext sql

from django.conf import settings
connection_string = 'postgresql+psycopg2://{USER}:{PASSWORD}@{HOST}:{PORT}/{NAME}'.format(
    **settings.DATABASES['default']
)
%sql $connection_string

"""
Explanation: Late contributions Received and Made
Setup
End of explanation
"""


%%sql
SELECT "FILING_ID", "AMEND_ID", "LINE_ITEM", COUNT(*)
FROM "S497_CD"
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

"""
Explanation: Unique Composite Key
The documentation says that the records are unique on the following fields:
* FILING_ID
* AMEND_ID
* LINE_ITEM
* REC_TYPE
* FORM_TYPE
REC_TYPE is always the same value: S497, so we can ignore this column. 
FORM_TYPE is either F497P1 or F497P2, indicating in whether itemized transaction is listed under Part 1 (Contributions Received) or Part 2 (Contributions Made). I'll split these up into separate tables.
Are the S497_CD records actually unique on FILING_ID, AMEND_ID and LINE_ITEM?
Yes. And this is even true across the Parts 1 and 2 (Contributions Received and Contributions Made).
End of explanation
"""


%%sql
SELECT COUNT(*)
FROM "S497_CD"
WHERE "TRAN_ID" IS NULL OR "TRAN_ID" = '' OR "TRAN_ID" = '0';

"""
Explanation: TRAN_ID
The S497_CD table includes a TRAN_ID field, which the documentation describes as a "Permanent value unique to this item".
Is TRAN_ID ever NULL or blank?
No.
End of explanation
"""


%%sql
SELECT "TRAN_ID", COUNT(DISTINCT "FILING_ID") 
FROM "S497_CD"
GROUP BY 1
HAVING COUNT(DISTINCT "FILING_ID") > 1
ORDER BY COUNT(DISTINCT "FILING_ID") DESC
LIMIT 100;

"""
Explanation: Is TRAN_ID unique across filings?
Decidedly no.
End of explanation
"""


%%sql
SELECT "FILING_ID", "TRAN_ID", COUNT(DISTINCT "AMEND_ID") AS amend_count, COUNT(*) AS row_count
FROM "S497_CD"
GROUP BY 1, 2
ORDER BY COUNT(*) DESC
LIMIT 100;

"""
Explanation: But TRAN_ID does appear to be unique within each filing amendment, and appears to be reused for each filing.
End of explanation
"""


%%sql
SELECT "FILING_ID", "TRAN_ID", "AMEND_ID", COUNT(*)
FROM "S497_CD"
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1;

"""
Explanation: There's one exception:
End of explanation
"""


%%sql
SELECT *
FROM "S497_CD"
WHERE "FILING_ID" = 2072379
AND "TRAN_ID" = 'EXP9671';

"""
Explanation: Looks like this TRAN_ID is duplicated across the two parts of the filing. So it was both a contribution both made and received?
End of explanation
"""


