Schema for opdr

CREATE TABLE ar_assets_other (
oid INTEGER NOT NULL PRIMARY KEY, description TEXT NULL, book_value INTEGER NULL, rpt_id INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_assets_total (
rpt_id INTEGER NOT NULL, cash_start INTEGER NULL, cash_end INTEGER NULL, accounts_receivable_start INTEGER NULL, accounts_receivable_end INTEGER NULL, investments_start INTEGER NULL, investments_end INTEGER NULL, fixed_assets_start INTEGER NULL, fixed_assets_end INTEGER NULL, treasury_securities_start INTEGER NULL, treasury_securities_end INTEGER NULL, other_assets_start INTEGER NULL, other_assets_end INTEGER NULL, loans_receivable_start INTEGER NULL, loans_receivable_end INTEGER NULL, total_start INTEGER NULL, market_sec_total_cost INTEGER NULL, market_sec_total_book_value INTEGER NULL, other_inv_total_cost INTEGER NULL, other_inv_total_book_value INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_disbursements_benefits (
oid INTEGER NOT NULL PRIMARY KEY, description TEXT NULL, paid_to TEXT NULL, amount INTEGER NULL, rpt_id INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_disbursements_total (
rpt_id INTEGER NOT NULL, representational INTEGER NULL, political INTEGER NULL, contributions INTEGER NULL, general_overhead INTEGER NULL, union_administration INTEGER NULL, withheld INTEGER NULL, members INTEGER NULL, supplies INTEGER NULL, fees INTEGER NULL, administration INTEGER NULL, direct_taxes INTEGER NULL, strike_benefits INTEGER NULL, per_capita_tax INTEGER NULL, to_officers INTEGER NULL, investments INTEGER NULL, benefits INTEGER NULL, loans_made INTEGER NULL, loans_payment INTEGER NULL, affiliates INTEGER NULL, other_disbursements INTEGER NULL, to_employees INTEGER NULL, less_total_disbursed INTEGER NULL, withheld_not_disbursed INTEGER NULL, officer_deductions INTEGER NULL, employee_deductions INTEGER NULL, inv_purch_reinvestments INTEGER NULL, all_other_rep_activities INTEGER NULL, all_other_pol_activities INTEGER NULL, all_other_contributions INTEGER NULL, all_other_gen_overhead INTEGER NULL, all_other_union_admin INTEGER NULL, off_admin_expense INTEGER NULL, edu_pub_expense INTEGER NULL, pro_fees INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_liabilities_loans_paybl (
oid INTEGER NOT NULL PRIMARY KEY, source TEXT NULL, loans_owed_start INTEGER NULL, loans_obtained INTEGER NULL, cash_repayment INTEGER NULL, non_cash_repayment INTEGER NULL, loans_owed_end INTEGER NULL, rpt_id INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_liabilities_other (
oid INTEGER NOT NULL PRIMARY KEY, description TEXT NULL, amount INTEGER NULL, rpt_id INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_liabilities_total (
rpt_id INTEGER NOT NULL, accounts_payable_start INTEGER NULL, accounts_payable_end INTEGER NULL, loans_payable_start INTEGER NULL, loans_payable_end INTEGER NULL, mortgage_payable_start INTEGER NULL, mortgage_payable_end INTEGER NULL, other_liabilities_start INTEGER NULL, other_liabilities_end INTEGER NULL, total_start INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE ar_receipts_total (
rpt_id INTEGER NOT NULL, dues INTEGER NULL, tax INTEGER NULL, investments INTEGER NULL, supplies INTEGER NULL, loans_made INTEGER NULL, interest INTEGER NULL, dividends INTEGER NULL, rents INTEGER NULL, fees INTEGER NULL, loans_obtained INTEGER NULL, other_receipts INTEGER NULL, affiliates INTEGER NULL, members INTEGER NULL, inv_sale_reinvestments INTEGER NULL, all_other_receipts INTEGER NULL, FOREIGN KEY(rpt_id) REFERENCES lm_data(rpt_id)
);
CREATE TABLE "ar_erds_codes" (
   [code_type] TEXT,
   [code] INTEGER PRIMARY KEY,
   [name] TEXT,
   [code_description] TEXT
);
CREATE TABLE "ar_payer_payee" (
   [payer_payee_id] INTEGER PRIMARY KEY NOT NULL,
   [payer_payee_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [rcpt_disb_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [name] TEXT,
   [po_box] TEXT,
   [street] TEXT,
   [city] TEXT,
   [state] TEXT,
   [zip] TEXT,
   [type_or_class] TEXT,
   [itemized] INTEGER,
   [non_itemized] INTEGER,
   [total] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_receipts_other" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [receipt_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [purpose] TEXT,
   [date] TEXT,
   [amount] INTEGER,
   [payer_payee_id] INTEGER REFERENCES [ar_payer_payee]([payer_payee_id]),
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_assets_accts_rcvbl" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [acct_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [name] TEXT,
   [past_due_90] INTEGER,
   [past_due_180] INTEGER,
   [liquidated] INTEGER,
   [total] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_assets_fixed" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [description] TEXT,
   [cost_basis] INTEGER,
   [depreciation] INTEGER,
   [book_value] INTEGER,
   [value] INTEGER,
   [asset_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_assets_investments" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [inv_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [name] TEXT,
   [amount] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_assets_loans_rcvbl" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [loan_type] INTEGER REFERENCES [ar_erds_codes]([code]),
   [name] TEXT,
   [purpose] TEXT,
   [security] TEXT,
   [terms] TEXT,
   [outstanding_start_amt] INTEGER,
   [outstanding_end_amt] INTEGER,
   [new_loan_amt] INTEGER,
   [cash_repayments] INTEGER,
   [non_cash_repayments] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_disbursements_emp_off" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [emp_off_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [first_name] TEXT,
   [middle_name] TEXT,
   [last_name] TEXT,
   [title] TEXT,
   [status_other_payer] TEXT,
   [gross_salary] INTEGER,
   [allowances] INTEGER,
   [official_business] INTEGER,
   [other_not_rptd] INTEGER,
   [total] INTEGER,
   [rep_pct] INTEGER,
   [pol_pct] INTEGER,
   [cont_pct] INTEGER,
   [gen_ovrhd_pct] INTEGER,
   [admin_pct] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id]),
   [item_num] INTEGER
);
CREATE TABLE "ar_disbursements_genrl" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [disbursement_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [purpose] TEXT,
   [date] TEXT,
   [amount] INTEGER,
   [payer_payee_id] INTEGER REFERENCES [ar_payer_payee]([payer_payee_id]),
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_disbursements_inv_purchases" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [description] TEXT,
   [cost] INTEGER,
   [book_value] INTEGER,
   [cash_paid] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_liabilities_accts_paybl" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [acct_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [name] TEXT,
   [total] INTEGER,
   [past_due_90] INTEGER,
   [past_due_180] INTEGER,
   [liquidated] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_membership" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [membership_type] INTEGER REFERENCES [ar_erds_codes]([code]),
   [category] TEXT,
   [number] INTEGER,
   [voting_eligibility] TEXT,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_rates_dues_fees" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [rate_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [amount] TEXT,
   [unit] TEXT,
   [minimum] TEXT,
   [maximum] TEXT,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "ar_receipts_inv_fa_sales" (
   [oid] INTEGER PRIMARY KEY NOT NULL,
   [description] TEXT,
   [cost] INTEGER,
   [book_value] INTEGER,
   [gross_sales_price] INTEGER,
   [amount_recd] INTEGER,
   [rpt_id] INTEGER REFERENCES [lm_data]([rpt_id])
);
CREATE TABLE "lm_data" (
   [union_name] TEXT NOT NULL,
   [aff_abbr] TEXT NOT NULL,
   [f_num] INTEGER NOT NULL,
   [fye] INTEGER REFERENCES [ar_erds_codes]([code]),
   [unit_name] TEXT,
   [est_date] TEXT,
   [term_date] TEXT,
   [shortage] TEXT,
   [maximum_bond] INTEGER,
   [pd_covered_from] TEXT,
   [pd_covered_to] TEXT,
   [constitution_bylaw] TEXT,
   [terminate] TEXT,
   [ttl_assets] INTEGER,
   [subsidiary] TEXT,
   [desiq_pre] TEXT,
   [desig_num] INTEGER,
   [desig_suf] TEXT,
   [desig_name] TEXT,
   [ttl_liabilities] INTEGER,
   [ttl_receipts] INTEGER,
   [ttl_disbursements] INTEGER,
   [members] INTEGER,
   [register_date] TEXT,
   [amended] TEXT,
   [hardship] TEXT,
   [has_trust] TEXT,
   [pac_funds] TEXT,
   [outside_audit] TEXT,
   [has_property_change] TEXT,
   [assets_pledged] TEXT,
   [contingent] TEXT,
   [next_election] TEXT,
   [has_liquidated_liabilities] TEXT,
   [has_extended_loan_credit] TEXT,
   [has_liquidated_receivables] TEXT,
   [has_subsidiary] TEXT,
   [num_attachments] INTEGER,
   [rpt_id] INTEGER PRIMARY KEY NOT NULL,
   [yr_covered] INTEGER,
   [amendment] INTEGER NOT NULL,
   [receive_date] TEXT,
   [adr_id] INTEGER NOT NULL,
   [address_type] INTEGER NOT NULL REFERENCES [ar_erds_codes]([code]),
   [mail_firstname] TEXT,
   [mail_lastname] TEXT,
   [build_num] TEXT,
   [street_adr] TEXT,
   [city] TEXT,
   [state] TEXT,
   [zip] TEXT,
   [voice] TEXT,
   [mod_date] TEXT,
   [mod_id] INTEGER,
   [record_kept] TEXT,
   [form_type] TEXT NOT NULL
);
CREATE TABLE 'lm_data_fts_data'(id INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE 'lm_data_fts_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
CREATE TABLE 'lm_data_fts_docsize'(id INTEGER PRIMARY KEY, sz BLOB);
CREATE TABLE 'lm_data_fts_config'(k PRIMARY KEY, v) WITHOUT ROWID;
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE VIRTUAL TABLE [lm_data_fts] USING FTS5 (
    [union_name], [aff_abbr], [unit_name], [desiq_pre], [desig_num], [desig_suf], [street_adr], [city], [state], [zip],
    content=[lm_data]
)