from sqlalchemy import (ARRAY, Boolean, Column, Date, DateTime, ForeignKey, Index, Integer, BigInteger, Numeric, Text,
                        func)
from sqlalchemy.orm import relationship

from dataactcore.models.baseModel import Base
from dataactcore.models.domainModels import concat_tas, concat_display_tas


class FlexField(Base):
    """Model for the flex field table."""
    __tablename__ = "flex_field"

    flex_field_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_flex_field_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False)
    header = Column(Text)
    cell = Column(Text)
    file_type_id = Column(Integer)


class Appropriation(Base):
    """Model for the appropriation table."""
    __tablename__ = "appropriation"

    appropriation_id = Column(Integer, primary_key=True)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_appropriation_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    adjustments_to_unobligated_cpe = Column(Numeric)
    agency_identifier = Column(Text)
    allocation_transfer_agency = Column(Text)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    borrowing_authority_amount_cpe = Column(Numeric)
    budget_authority_appropria_cpe = Column(Numeric)
    total_budgetary_resources_cpe = Column(Numeric)
    budget_authority_unobligat_fyb = Column(Numeric)
    contract_authority_amount_cpe = Column(Numeric)
    deobligations_recoveries_r_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    gross_outlay_amount_by_tas_cpe = Column(Numeric)
    main_account_code = Column(Text)
    obligations_incurred_total_cpe = Column(Numeric)
    other_budgetary_resources_cpe = Column(Numeric)
    spending_authority_from_of_cpe = Column(Numeric)
    status_of_budgetary_resour_cpe = Column(Numeric)
    sub_account_code = Column(Text)
    unobligated_balance_cpe = Column(Numeric)
    tas = Column(Text, index=True, nullable=False, default=concat_tas)
    display_tas = Column(Text, default=concat_display_tas)
    account_num = Column(Integer, nullable=True, index=True)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(Appropriation, self).__init__(**clean_kwargs)


Index("ix_appropriation_account_num_submission_id",
      Appropriation.account_num,
      Appropriation.submission_id,
      unique=False)


class ObjectClassProgramActivity(Base):
    """Model for the object_class_program_activity table."""
    __tablename__ = "object_class_program_activity"

    object_class_program_activity_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_object_class_program_activity_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    agency_identifier = Column(Text)
    allocation_transfer_agency = Column(Text)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    by_direct_reimbursable_fun = Column(Text, index=True)
    deobligations_recov_by_pro_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    gross_outlay_amount_by_pro_cpe = Column(Numeric)
    gross_outlay_amount_by_pro_fyb = Column(Numeric)
    gross_outlays_delivered_or_cpe = Column(Numeric)
    gross_outlays_delivered_or_fyb = Column(Numeric)
    gross_outlays_undelivered_cpe = Column(Numeric)
    gross_outlays_undelivered_fyb = Column(Numeric)
    main_account_code = Column(Text)
    object_class = Column(Text, index=True)
    obligations_delivered_orde_cpe = Column(Numeric)
    obligations_delivered_orde_fyb = Column(Numeric)
    obligations_incurred_by_pr_cpe = Column(Numeric)
    obligations_undelivered_or_cpe = Column(Numeric)
    obligations_undelivered_or_fyb = Column(Numeric)
    program_activity_code = Column(Text, index=True)
    program_activity_name = Column(Text)
    sub_account_code = Column(Text)
    ussgl480100_undelivered_or_cpe = Column(Numeric)
    ussgl480100_undelivered_or_fyb = Column(Numeric)
    ussgl480200_undelivered_or_cpe = Column(Numeric)
    ussgl480200_undelivered_or_fyb = Column(Numeric)
    ussgl483100_undelivered_or_cpe = Column(Numeric)
    ussgl483200_undelivered_or_cpe = Column(Numeric)
    ussgl487100_downward_adjus_cpe = Column(Numeric)
    ussgl487200_downward_adjus_cpe = Column(Numeric)
    ussgl488100_upward_adjustm_cpe = Column(Numeric)
    ussgl488200_upward_adjustm_cpe = Column(Numeric)
    ussgl490100_delivered_orde_cpe = Column(Numeric)
    ussgl490100_delivered_orde_fyb = Column(Numeric)
    ussgl490200_delivered_orde_cpe = Column(Numeric)
    ussgl490800_authority_outl_cpe = Column(Numeric)
    ussgl490800_authority_outl_fyb = Column(Numeric)
    ussgl493100_delivered_orde_cpe = Column(Numeric)
    ussgl497100_downward_adjus_cpe = Column(Numeric)
    ussgl497200_downward_adjus_cpe = Column(Numeric)
    ussgl498100_upward_adjustm_cpe = Column(Numeric)
    ussgl498200_upward_adjustm_cpe = Column(Numeric)
    tas = Column(Text, nullable=False, default=concat_tas)
    display_tas = Column(Text, default=concat_display_tas)
    account_num = Column(Integer, nullable=True)
    disaster_emergency_fund_code = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(ObjectClassProgramActivity, self).__init__(**clean_kwargs)


Index("ix_oc_pa_tas_oc_pa",
      ObjectClassProgramActivity.tas,
      ObjectClassProgramActivity.object_class,
      ObjectClassProgramActivity.program_activity_code,
      unique=False)

Index("ix_oc_pa_account_num_submission_id",
      ObjectClassProgramActivity.account_num,
      ObjectClassProgramActivity.submission_id,
      unique=False)

Index("ix_oc_pa_pan_upper", func.upper(ObjectClassProgramActivity.program_activity_name))
Index("ix_oc_atc_upper", func.upper(ObjectClassProgramActivity.availability_type_code))
Index("ix_oc_dr_upper", func.upper(ObjectClassProgramActivity.by_direct_reimbursable_fun))
Index("ix_oc_pa_defc_upper", func.upper(ObjectClassProgramActivity.disaster_emergency_fund_code))


class AwardFinancial(Base):
    """Corresponds to entries in File C"""
    __tablename__ = "award_financial"

    award_financial_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_award_financial_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    agency_identifier = Column(Text, index=True)
    allocation_transfer_agency = Column(Text, index=True)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    by_direct_reimbursable_fun = Column(Text)
    deobligations_recov_by_awa_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    fain = Column(Text, index=True)
    gross_outlay_amount_by_awa_cpe = Column(Numeric)
    gross_outlay_amount_by_awa_fyb = Column(Numeric)
    gross_outlays_delivered_or_cpe = Column(Numeric)
    gross_outlays_delivered_or_fyb = Column(Numeric)
    gross_outlays_undelivered_cpe = Column(Numeric)
    gross_outlays_undelivered_fyb = Column(Numeric)
    main_account_code = Column(Text, index=True)
    object_class = Column(Text, index=True)
    obligations_delivered_orde_cpe = Column(Numeric)
    obligations_delivered_orde_fyb = Column(Numeric)
    obligations_incurred_byawa_cpe = Column(Numeric)
    obligations_undelivered_or_cpe = Column(Numeric)
    obligations_undelivered_or_fyb = Column(Numeric)
    parent_award_id = Column(Text, index=True)
    piid = Column(Text, index=True)
    program_activity_code = Column(Text, index=True)
    program_activity_name = Column(Text)
    sub_account_code = Column(Text)
    transaction_obligated_amou = Column(Numeric)
    uri = Column(Text, index=True)
    ussgl480100_undelivered_or_cpe = Column(Numeric)
    ussgl480100_undelivered_or_fyb = Column(Numeric)
    ussgl480200_undelivered_or_cpe = Column(Numeric)
    ussgl480200_undelivered_or_fyb = Column(Numeric)
    ussgl483100_undelivered_or_cpe = Column(Numeric)
    ussgl483200_undelivered_or_cpe = Column(Numeric)
    ussgl487100_downward_adjus_cpe = Column(Numeric)
    ussgl487200_downward_adjus_cpe = Column(Numeric)
    ussgl488100_upward_adjustm_cpe = Column(Numeric)
    ussgl488200_upward_adjustm_cpe = Column(Numeric)
    ussgl490100_delivered_orde_cpe = Column(Numeric)
    ussgl490100_delivered_orde_fyb = Column(Numeric)
    ussgl490200_delivered_orde_cpe = Column(Numeric)
    ussgl490800_authority_outl_cpe = Column(Numeric)
    ussgl490800_authority_outl_fyb = Column(Numeric)
    ussgl493100_delivered_orde_cpe = Column(Numeric)
    ussgl497100_downward_adjus_cpe = Column(Numeric)
    ussgl497200_downward_adjus_cpe = Column(Numeric)
    ussgl498100_upward_adjustm_cpe = Column(Numeric)
    ussgl498200_upward_adjustm_cpe = Column(Numeric)
    tas = Column(Text, nullable=False, default=concat_tas, index=True)
    display_tas = Column(Text, default=concat_display_tas)
    account_num = Column(Integer, nullable=True, index=True)
    general_ledger_post_date = Column(Date)
    disaster_emergency_fund_code = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(AwardFinancial, self).__init__(**clean_kwargs)


Index("ix_award_financial_tas_oc_pa",
      AwardFinancial.tas,
      AwardFinancial.object_class,
      AwardFinancial.program_activity_code,
      unique=False)
Index("ix_af_piid_upper", func.upper(AwardFinancial.piid))
Index("ix_af_parent_award_id_upper", func.upper(AwardFinancial.parent_award_id))
Index("ix_af_fain_upper", func.upper(AwardFinancial.fain))
Index("ix_af_uri_upper", func.upper(AwardFinancial.uri))
Index("ix_af_pan_upper", func.upper(AwardFinancial.program_activity_name))
Index("ix_af_atc_upper", func.upper(AwardFinancial.availability_type_code))
Index("ix_af_dr_upper", func.upper(AwardFinancial.by_direct_reimbursable_fun))
Index("ix_af_defc_upper", func.upper(AwardFinancial.disaster_emergency_fund_code))


class TotalObligations(Base):
    """ Model keeping track of the total obligations from the AwardFinancial (C file) table. """
    __tablename__ = "total_obligations"

    total_obligations_id = Column(Integer, primary_key=True)
    submission_id = Column(Integer, ForeignKey("submission.submission_id", ondelete="CASCADE",
                                               name="fk_total_obligations_submission_id"),
                           nullable=False, unique=True, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    total_obligations = Column(Numeric)
    total_proc_obligations = Column(Numeric)
    total_asst_obligations = Column(Numeric)


class PublishedTotalObligations(Base):
    """ Model keeping track of the published total obligations from the AwardFinancial (C file) table. """
    __tablename__ = "published_total_obligations"

    published_total_obligations_id = Column(Integer, primary_key=True)
    submission_id = Column(Integer, ForeignKey("submission.submission_id", ondelete="CASCADE",
                                               name="fk_published_total_obligations_submission_id"),
                           nullable=False, unique=True, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    total_obligations = Column(Numeric)
    total_proc_obligations = Column(Numeric)
    total_asst_obligations = Column(Numeric)


class PublishedFlexField(Base):
    """ Model for the published flex field table. """
    __tablename__ = "published_flex_field"

    published_flex_field_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_published_flex_field_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer)
    row_number = Column(Integer)
    header = Column(Text)
    cell = Column(Text)
    file_type_id = Column(Integer)


class PublishedAppropriation(Base):
    """Model for the published data from the Appropriation (A file) table."""
    __tablename__ = "published_appropriation"

    published_appropriation_id = Column(Integer, primary_key=True)
    submission_id = Column(Integer, ForeignKey("submission.submission_id", ondelete="CASCADE",
                                               name="fk_published_appropriation_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer)
    row_number = Column(Integer)
    adjustments_to_unobligated_cpe = Column(Numeric)
    agency_identifier = Column(Text)
    allocation_transfer_agency = Column(Text)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    borrowing_authority_amount_cpe = Column(Numeric)
    budget_authority_appropria_cpe = Column(Numeric)
    total_budgetary_resources_cpe = Column(Numeric)
    budget_authority_unobligat_fyb = Column(Numeric)
    contract_authority_amount_cpe = Column(Numeric)
    deobligations_recoveries_r_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    gross_outlay_amount_by_tas_cpe = Column(Numeric)
    main_account_code = Column(Text)
    obligations_incurred_total_cpe = Column(Numeric)
    other_budgetary_resources_cpe = Column(Numeric)
    spending_authority_from_of_cpe = Column(Numeric)
    status_of_budgetary_resour_cpe = Column(Numeric)
    sub_account_code = Column(Text)
    unobligated_balance_cpe = Column(Numeric)
    tas = Column(Text)
    account_num = Column(Integer)
    display_tas = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedAppropriation, self).__init__(**clean_kwargs)


class PublishedObjectClassProgramActivity(Base):
    """Model for the published data from the ObjectClassProgramActivity (B file) table."""
    __tablename__ = "published_object_class_program_activity"

    published_object_class_program_activity_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer, ForeignKey("submission.submission_id", ondelete="CASCADE",
                                               name="fk_published_object_class_program_activity_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer)
    row_number = Column(Integer)
    agency_identifier = Column(Text)
    allocation_transfer_agency = Column(Text)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    by_direct_reimbursable_fun = Column(Text)
    deobligations_recov_by_pro_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    gross_outlay_amount_by_pro_cpe = Column(Numeric)
    gross_outlay_amount_by_pro_fyb = Column(Numeric)
    gross_outlays_delivered_or_cpe = Column(Numeric)
    gross_outlays_delivered_or_fyb = Column(Numeric)
    gross_outlays_undelivered_cpe = Column(Numeric)
    gross_outlays_undelivered_fyb = Column(Numeric)
    main_account_code = Column(Text)
    object_class = Column(Text)
    obligations_delivered_orde_cpe = Column(Numeric)
    obligations_delivered_orde_fyb = Column(Numeric)
    obligations_incurred_by_pr_cpe = Column(Numeric)
    obligations_undelivered_or_cpe = Column(Numeric)
    obligations_undelivered_or_fyb = Column(Numeric)
    program_activity_code = Column(Text)
    program_activity_name = Column(Text)
    sub_account_code = Column(Text)
    ussgl480100_undelivered_or_cpe = Column(Numeric)
    ussgl480100_undelivered_or_fyb = Column(Numeric)
    ussgl480200_undelivered_or_cpe = Column(Numeric)
    ussgl480200_undelivered_or_fyb = Column(Numeric)
    ussgl483100_undelivered_or_cpe = Column(Numeric)
    ussgl483200_undelivered_or_cpe = Column(Numeric)
    ussgl487100_downward_adjus_cpe = Column(Numeric)
    ussgl487200_downward_adjus_cpe = Column(Numeric)
    ussgl488100_upward_adjustm_cpe = Column(Numeric)
    ussgl488200_upward_adjustm_cpe = Column(Numeric)
    ussgl490100_delivered_orde_cpe = Column(Numeric)
    ussgl490100_delivered_orde_fyb = Column(Numeric)
    ussgl490200_delivered_orde_cpe = Column(Numeric)
    ussgl490800_authority_outl_cpe = Column(Numeric)
    ussgl490800_authority_outl_fyb = Column(Numeric)
    ussgl493100_delivered_orde_cpe = Column(Numeric)
    ussgl497100_downward_adjus_cpe = Column(Numeric)
    ussgl497200_downward_adjus_cpe = Column(Numeric)
    ussgl498100_upward_adjustm_cpe = Column(Numeric)
    ussgl498200_upward_adjustm_cpe = Column(Numeric)
    tas = Column(Text, default=concat_tas)
    account_num = Column(Integer)
    display_tas = Column(Text)
    disaster_emergency_fund_code = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedObjectClassProgramActivity, self).__init__(**clean_kwargs)


class PublishedAwardFinancial(Base):
    """Model for the published data from the AwardFinancial (C file) table."""
    __tablename__ = "published_award_financial"

    published_award_financial_id = Column(BigInteger, primary_key=True)
    submission_id = Column(Integer, ForeignKey("submission.submission_id", ondelete="CASCADE",
                                               name="fk_published_award_financial_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer)
    row_number = Column(Integer)
    agency_identifier = Column(Text)
    allocation_transfer_agency = Column(Text)
    availability_type_code = Column(Text)
    beginning_period_of_availa = Column(Text)
    by_direct_reimbursable_fun = Column(Text)
    deobligations_recov_by_awa_cpe = Column(Numeric)
    ending_period_of_availabil = Column(Text)
    fain = Column(Text)
    gross_outlay_amount_by_awa_cpe = Column(Numeric)
    gross_outlay_amount_by_awa_fyb = Column(Numeric)
    gross_outlays_delivered_or_cpe = Column(Numeric)
    gross_outlays_delivered_or_fyb = Column(Numeric)
    gross_outlays_undelivered_cpe = Column(Numeric)
    gross_outlays_undelivered_fyb = Column(Numeric)
    main_account_code = Column(Text)
    object_class = Column(Text)
    obligations_delivered_orde_cpe = Column(Numeric)
    obligations_delivered_orde_fyb = Column(Numeric)
    obligations_incurred_byawa_cpe = Column(Numeric)
    obligations_undelivered_or_cpe = Column(Numeric)
    obligations_undelivered_or_fyb = Column(Numeric)
    parent_award_id = Column(Text)
    piid = Column(Text)
    program_activity_code = Column(Text)
    program_activity_name = Column(Text)
    sub_account_code = Column(Text)
    transaction_obligated_amou = Column(Numeric)
    uri = Column(Text)
    ussgl480100_undelivered_or_cpe = Column(Numeric)
    ussgl480100_undelivered_or_fyb = Column(Numeric)
    ussgl480200_undelivered_or_cpe = Column(Numeric)
    ussgl480200_undelivered_or_fyb = Column(Numeric)
    ussgl483100_undelivered_or_cpe = Column(Numeric)
    ussgl483200_undelivered_or_cpe = Column(Numeric)
    ussgl487100_downward_adjus_cpe = Column(Numeric)
    ussgl487200_downward_adjus_cpe = Column(Numeric)
    ussgl488100_upward_adjustm_cpe = Column(Numeric)
    ussgl488200_upward_adjustm_cpe = Column(Numeric)
    ussgl490100_delivered_orde_cpe = Column(Numeric)
    ussgl490100_delivered_orde_fyb = Column(Numeric)
    ussgl490200_delivered_orde_cpe = Column(Numeric)
    ussgl490800_authority_outl_cpe = Column(Numeric)
    ussgl490800_authority_outl_fyb = Column(Numeric)
    ussgl493100_delivered_orde_cpe = Column(Numeric)
    ussgl497100_downward_adjus_cpe = Column(Numeric)
    ussgl497200_downward_adjus_cpe = Column(Numeric)
    ussgl498100_upward_adjustm_cpe = Column(Numeric)
    ussgl498200_upward_adjustm_cpe = Column(Numeric)
    tas = Column(Text)
    account_num = Column(Integer)
    general_ledger_post_date = Column(Date)
    display_tas = Column(Text)
    disaster_emergency_fund_code = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedAwardFinancial, self).__init__(**clean_kwargs)


class AwardFinancialAssistance(Base):
    """Model for D2-Award (Financial Assistance)."""
    __tablename__ = "award_financial_assistance"

    award_financial_assistance_id = Column(BigInteger, primary_key=True)
    afa_generated_unique = Column(Text)
    unique_award_key = Column(Text)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_award_financial_assistance_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    action_date = Column(Text, index=True)
    action_type = Column(Text, index=True)
    action_type_description = Column(Text)
    assistance_type = Column(Text, index=True)
    assistance_type_desc = Column(Text)
    award_description = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    awardee_or_recipient_duns = Column(Text, index=True)
    awardee_or_recipient_uei = Column(Text, index=True)
    awarding_agency_code = Column(Text, index=True)
    awarding_agency_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    awarding_sub_tier_agency_c = Column(Text, index=True)
    awarding_sub_tier_agency_n = Column(Text)
    award_modification_amendme = Column(Text)
    business_funds_indicator = Column(Text)
    business_funds_ind_desc = Column(Text)
    business_types = Column(Text)
    business_types_desc = Column(Text)
    cfda_number = Column(Text, index=True)
    cfda_title = Column(Text)
    correction_delete_indicatr = Column(Text, index=True)
    correction_delete_ind_desc = Column(Text)
    face_value_loan_guarantee = Column(Text)
    fain = Column(Text, index=True)
    federal_action_obligation = Column(Numeric, index=True)
    fiscal_year_and_quarter_co = Column(Text)
    funding_agency_code = Column(Text, index=True)
    funding_agency_name = Column(Text)
    funding_office_name = Column(Text)
    funding_office_code = Column(Text)
    funding_sub_tier_agency_co = Column(Text, index=True)
    funding_sub_tier_agency_na = Column(Text)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_city_code = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_county_code = Column(Text)
    legal_entity_county_name = Column(Text)
    legal_entity_foreign_city = Column(Text)
    legal_entity_foreign_posta = Column(Text)
    legal_entity_foreign_provi = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_name = Column(Text)
    legal_entity_zip5 = Column(Text)
    legal_entity_zip_last4 = Column(Text)
    non_federal_funding_amount = Column(Text)
    original_loan_subsidy_cost = Column(Text)
    period_of_performance_curr = Column(Text)
    period_of_performance_star = Column(Text)
    place_of_performance_city = Column(Text)
    place_of_performance_code = Column(Text)
    place_of_performance_congr = Column(Text)
    place_of_perform_country_c = Column(Text)
    place_of_perform_county_na = Column(Text)
    place_of_performance_forei = Column(Text)
    place_of_perform_state_nam = Column(Text)
    place_of_performance_zip4a = Column(Text)
    record_type = Column(Text, index=True)
    record_type_description = Column(Text)
    sai_number = Column(Text)
    total_funding_amount = Column(Text)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_duns = Column(Text)
    ultimate_parent_uei = Column(Text)
    uri = Column(Text, index=True)
    place_of_perform_county_co = Column(Text)
    place_of_perform_country_n = Column(Text)
    legal_entity_country_name = Column(Text)
    place_of_performance_scope = Column(Text)
    funding_opportunity_goals = Column(Text)
    funding_opportunity_number = Column(Text)
    indirect_federal_sharing = Column(Numeric)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(AwardFinancialAssistance, self).__init__(**clean_kwargs)


Index("ix_afa_fain_upper", func.upper(AwardFinancialAssistance.fain))


class AwardProcurement(Base):
    """Model for D1-Award (Procurement)."""
    __tablename__ = "award_procurement"
    award_procurement_id = Column(BigInteger, primary_key=True)
    detached_award_proc_unique = Column(Text)
    unique_award_key = Column(Text)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_award_procurement_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    piid = Column(Text, index=True)
    awarding_sub_tier_agency_c = Column(Text, index=True)
    awarding_sub_tier_agency_n = Column(Text)
    awarding_agency_code = Column(Text, index=True)
    awarding_agency_name = Column(Text)
    parent_award_id = Column(Text, index=True)
    award_modification_amendme = Column(Text)
    type_of_contract_pricing = Column(Text)
    type_of_contract_pric_desc = Column(Text)
    contract_award_type = Column(Text)
    contract_award_type_desc = Column(Text)
    naics = Column(Text)
    naics_description = Column(Text)
    awardee_or_recipient_uniqu = Column(Text, index=True)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_unique_ide = Column(Text)
    award_description = Column(Text)
    place_of_performance_zip4a = Column(Text)
    place_of_performance_city = Column(Text)
    place_of_performance_congr = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_descrip = Column(Text)
    legal_entity_zip4 = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_country_name = Column(Text)
    period_of_performance_star = Column(Text)
    period_of_performance_curr = Column(Text)
    period_of_perf_potential_e = Column(Text)
    ordering_period_end_date = Column(Text)
    action_date = Column(Text, index=True)
    action_type = Column(Text)
    action_type_description = Column(Text)
    federal_action_obligation = Column(Numeric)
    current_total_value_award = Column(Text)
    potential_total_value_awar = Column(Text)
    funding_sub_tier_agency_co = Column(Text, index=True)
    funding_sub_tier_agency_na = Column(Text)
    funding_office_code = Column(Text)
    funding_office_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    referenced_idv_agency_iden = Column(Text)
    funding_agency_code = Column(Text, index=True)
    funding_agency_name = Column(Text)
    place_of_performance_locat = Column(Text)
    place_of_performance_state = Column(Text)
    place_of_perform_country_c = Column(Text)
    idv_type = Column(Text)
    idv_type_description = Column(Text)
    entity_doing_business_as_n = Column(Text)
    entity_phone_number = Column(Text)
    entity_fax_number = Column(Text)
    multiple_or_single_award_i = Column(Text)
    multiple_or_single_aw_desc = Column(Text)
    referenced_mult_or_single = Column(Text)
    referenced_mult_or_si_desc = Column(Text)
    type_of_idc = Column(Text)
    type_of_idc_description = Column(Text)
    a_76_fair_act_action = Column(Text)
    a_76_fair_act_action_desc = Column(Text)
    dod_claimant_program_code = Column(Text)
    dod_claimant_prog_cod_desc = Column(Text)
    clinger_cohen_act_planning = Column(Text)
    clinger_cohen_act_pla_desc = Column(Text)
    commercial_item_acquisitio = Column(Text)
    commercial_item_acqui_desc = Column(Text)
    commercial_item_test_progr = Column(Text)
    commercial_item_test_desc = Column(Text)
    consolidated_contract = Column(Text)
    consolidated_contract_desc = Column(Text)
    contingency_humanitarian_o = Column(Text)
    contingency_humanitar_desc = Column(Text)
    contract_bundling = Column(Text)
    contract_bundling_descrip = Column(Text)
    contract_financing = Column(Text)
    contract_financing_descrip = Column(Text)
    contracting_officers_deter = Column(Text)
    contracting_officers_desc = Column(Text)
    cost_accounting_standards = Column(Text)
    cost_accounting_stand_desc = Column(Text)
    cost_or_pricing_data = Column(Text)
    cost_or_pricing_data_desc = Column(Text)
    country_of_product_or_serv = Column(Text)
    country_of_product_or_desc = Column(Text)
    construction_wage_rate_req = Column(Text)
    construction_wage_rat_desc = Column(Text)
    evaluated_preference = Column(Text)
    evaluated_preference_desc = Column(Text)
    extent_competed = Column(Text)
    extent_compete_description = Column(Text)
    contract_opp_notice = Column(Text)
    contract_opp_notice_desc = Column(Text)
    foreign_funding = Column(Text)
    foreign_funding_desc = Column(Text)
    government_furnished_prope = Column(Text)
    government_furnished_desc = Column(Text)
    information_technology_com = Column(Text)
    information_technolog_desc = Column(Text)
    interagency_contracting_au = Column(Text)
    interagency_contract_desc = Column(Text)
    local_area_set_aside = Column(Text)
    local_area_set_aside_desc = Column(Text)
    major_program = Column(Text)
    purchase_card_as_payment_m = Column(Text)
    purchase_card_as_paym_desc = Column(Text)
    multi_year_contract = Column(Text)
    multi_year_contract_desc = Column(Text)
    national_interest_action = Column(Text)
    national_interest_desc = Column(Text)
    number_of_actions = Column(Text)
    number_of_offers_received = Column(Text)
    other_statutory_authority = Column(Text)
    performance_based_service = Column(Text)
    performance_based_se_desc = Column(Text)
    place_of_manufacture = Column(Text)
    place_of_manufacture_desc = Column(Text)
    price_evaluation_adjustmen = Column(Text)
    product_or_service_code = Column(Text)
    product_or_service_co_desc = Column(Text)
    program_acronym = Column(Text)
    other_than_full_and_open_c = Column(Text)
    other_than_full_and_o_desc = Column(Text)
    recovered_materials_sustai = Column(Text)
    recovered_materials_s_desc = Column(Text)
    research = Column(Text)
    research_description = Column(Text)
    sea_transportation = Column(Text)
    sea_transportation_desc = Column(Text)
    labor_standards = Column(Text)
    labor_standards_descrip = Column(Text)
    small_business_competitive = Column(Boolean)
    solicitation_identifier = Column(Text)
    solicitation_date = Column(Text)
    solicitation_procedures = Column(Text)
    solicitation_procedur_desc = Column(Text)
    fair_opportunity_limited_s = Column(Text)
    fair_opportunity_limi_desc = Column(Text)
    subcontracting_plan = Column(Text)
    subcontracting_plan_desc = Column(Text)
    program_system_or_equipmen = Column(Text)
    program_system_or_equ_desc = Column(Text)
    type_set_aside = Column(Text)
    type_set_aside_description = Column(Text)
    epa_designated_product = Column(Text)
    epa_designated_produc_desc = Column(Text)
    materials_supplies_article = Column(Text)
    materials_supplies_descrip = Column(Text)
    transaction_number = Column(Text)
    sam_exception = Column(Text)
    sam_exception_description = Column(Text)
    city_local_government = Column(Boolean)
    county_local_government = Column(Boolean)
    inter_municipal_local_gove = Column(Boolean)
    local_government_owned = Column(Boolean)
    municipality_local_governm = Column(Boolean)
    school_district_local_gove = Column(Boolean)
    township_local_government = Column(Boolean)
    us_state_government = Column(Boolean)
    us_federal_government = Column(Boolean)
    federal_agency = Column(Boolean)
    federally_funded_research = Column(Boolean)
    us_tribal_government = Column(Boolean)
    foreign_government = Column(Boolean)
    community_developed_corpor = Column(Boolean)
    labor_surplus_area_firm = Column(Boolean)
    corporate_entity_not_tax_e = Column(Boolean)
    corporate_entity_tax_exemp = Column(Boolean)
    partnership_or_limited_lia = Column(Boolean)
    sole_proprietorship = Column(Boolean)
    small_agricultural_coopera = Column(Boolean)
    international_organization = Column(Boolean)
    us_government_entity = Column(Boolean)
    emerging_small_business = Column(Boolean)
    c8a_program_participant = Column(Boolean)
    sba_certified_8_a_joint_ve = Column(Boolean)
    dot_certified_disadvantage = Column(Boolean)
    self_certified_small_disad = Column(Boolean)
    historically_underutilized = Column(Boolean)
    small_disadvantaged_busine = Column(Boolean)
    the_ability_one_program = Column(Boolean)
    historically_black_college = Column(Boolean)
    c1862_land_grant_college = Column(Boolean)
    c1890_land_grant_college = Column(Boolean)
    c1994_land_grant_college = Column(Boolean)
    minority_institution = Column(Boolean)
    private_university_or_coll = Column(Boolean)
    school_of_forestry = Column(Boolean)
    state_controlled_instituti = Column(Boolean)
    tribal_college = Column(Boolean)
    veterinary_college = Column(Boolean)
    educational_institution = Column(Boolean)
    alaskan_native_servicing_i = Column(Boolean)
    community_development_corp = Column(Boolean)
    native_hawaiian_servicing = Column(Boolean)
    domestic_shelter = Column(Boolean)
    manufacturer_of_goods = Column(Boolean)
    hospital_flag = Column(Boolean)
    veterinary_hospital = Column(Boolean)
    hispanic_servicing_institu = Column(Boolean)
    foundation = Column(Boolean)
    woman_owned_business = Column(Boolean)
    minority_owned_business = Column(Boolean)
    women_owned_small_business = Column(Boolean)
    economically_disadvantaged = Column(Boolean)
    joint_venture_women_owned = Column(Boolean)
    joint_venture_economically = Column(Boolean)
    veteran_owned_business = Column(Boolean)
    service_disabled_veteran_o = Column(Boolean)
    contracts = Column(Boolean)
    grants = Column(Boolean)
    receives_contracts_and_gra = Column(Boolean)
    airport_authority = Column(Boolean)
    council_of_governments = Column(Boolean)
    housing_authorities_public = Column(Boolean)
    interstate_entity = Column(Boolean)
    planning_commission = Column(Boolean)
    port_authority = Column(Boolean)
    transit_authority = Column(Boolean)
    subchapter_s_corporation = Column(Boolean)
    limited_liability_corporat = Column(Boolean)
    foreign_owned_and_located = Column(Boolean)
    american_indian_owned_busi = Column(Boolean)
    alaskan_native_owned_corpo = Column(Boolean)
    indian_tribe_federally_rec = Column(Boolean)
    native_hawaiian_owned_busi = Column(Boolean)
    tribally_owned_business = Column(Boolean)
    asian_pacific_american_own = Column(Boolean)
    black_american_owned_busin = Column(Boolean)
    hispanic_american_owned_bu = Column(Boolean)
    native_american_owned_busi = Column(Boolean)
    subcontinent_asian_asian_i = Column(Boolean)
    other_minority_owned_busin = Column(Boolean)
    for_profit_organization = Column(Boolean)
    nonprofit_organization = Column(Boolean)
    other_not_for_profit_organ = Column(Boolean)
    us_local_government = Column(Boolean)
    referenced_idv_modificatio = Column(Text)
    undefinitized_action = Column(Text)
    undefinitized_action_desc = Column(Text)
    domestic_or_foreign_entity = Column(Text)
    domestic_or_foreign_e_desc = Column(Text)
    referenced_idv_type = Column(Text)
    referenced_idv_type_desc = Column(Text)
    referenced_idv_agency_name = Column(Text)
    award_or_idv_flag = Column(Text)
    place_of_perform_country_n = Column(Text)
    place_of_perform_state_nam = Column(Text)
    place_of_perform_county_na = Column(Text)
    base_exercised_options_val = Column(Text)
    base_and_all_options_value = Column(Text)
    cage_code = Column(Text)
    inherently_government_func = Column(Text)
    inherently_government_desc = Column(Text)
    organizational_type = Column(Text)
    number_of_employees = Column(Text)
    annual_revenue = Column(Text)
    total_obligated_amount = Column(Text)
    last_modified = Column(Text)
    additional_reporting = Column(Text)
    awardee_or_recipient_uei = Column(Text, index=True)
    ultimate_parent_uei = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(AwardProcurement, self).__init__(**clean_kwargs)


Index("ix_ap_piid_upper", func.upper(AwardProcurement.piid))
Index("ix_ap_parent_award_id_upper", func.upper(AwardProcurement.parent_award_id))
Index("ix_ap_awarding_sub_tier_agency_c_upper", func.upper(AwardProcurement.awarding_sub_tier_agency_c))


class PublishedAwardFinancialAssistance(Base):
    """ Model for Published D2-Award (Financial Assistance). """
    __tablename__ = 'published_award_financial_assistance'

    published_award_financial_assistance_id = Column(BigInteger, primary_key=True)
    afa_generated_unique = Column(Text)
    unique_award_key = Column(Text)
    submission_id = Column(Integer,
                           ForeignKey('submission.submission_id', ondelete='CASCADE',
                                      name='fk_published_award_financial_assistance_submission_id'),
                           nullable=False, index=True)
    submission = relationship('Submission', uselist=False, cascade='delete')
    job_id = Column(Integer)
    row_number = Column(Integer)
    action_date = Column(Text)
    action_type = Column(Text)
    action_type_description = Column(Text)
    assistance_type = Column(Text)
    assistance_type_desc = Column(Text)
    award_description = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    awardee_or_recipient_duns = Column(Text)
    awardee_or_recipient_uei = Column(Text)
    awarding_agency_code = Column(Text)
    awarding_agency_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    awarding_sub_tier_agency_c = Column(Text)
    awarding_sub_tier_agency_n = Column(Text)
    award_modification_amendme = Column(Text)
    business_funds_indicator = Column(Text)
    business_funds_ind_desc = Column(Text)
    business_types = Column(Text)
    business_types_desc = Column(Text)
    cfda_number = Column(Text)
    cfda_title = Column(Text)
    correction_delete_indicatr = Column(Text)
    correction_delete_ind_desc = Column(Text)
    face_value_loan_guarantee = Column(Text)
    fain = Column(Text)
    federal_action_obligation = Column(Numeric)
    fiscal_year_and_quarter_co = Column(Text)
    funding_agency_code = Column(Text)
    funding_agency_name = Column(Text)
    funding_office_name = Column(Text)
    funding_office_code = Column(Text)
    funding_sub_tier_agency_co = Column(Text)
    funding_sub_tier_agency_na = Column(Text)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_city_code = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_county_code = Column(Text)
    legal_entity_county_name = Column(Text)
    legal_entity_foreign_city = Column(Text)
    legal_entity_foreign_posta = Column(Text)
    legal_entity_foreign_provi = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_name = Column(Text)
    legal_entity_zip5 = Column(Text)
    legal_entity_zip_last4 = Column(Text)
    non_federal_funding_amount = Column(Text)
    original_loan_subsidy_cost = Column(Text)
    period_of_performance_curr = Column(Text)
    period_of_performance_star = Column(Text)
    place_of_performance_city = Column(Text)
    place_of_performance_code = Column(Text)
    place_of_performance_congr = Column(Text)
    place_of_perform_country_c = Column(Text)
    place_of_perform_county_na = Column(Text)
    place_of_performance_forei = Column(Text)
    place_of_perform_state_nam = Column(Text)
    place_of_performance_zip4a = Column(Text)
    record_type = Column(Text)
    record_type_description = Column(Text)
    sai_number = Column(Text)
    total_funding_amount = Column(Text)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_duns = Column(Text)
    ultimate_parent_uei = Column(Text)
    uri = Column(Text)
    place_of_perform_county_co = Column(Text)
    place_of_perform_country_n = Column(Text)
    legal_entity_country_name = Column(Text)
    place_of_performance_scope = Column(Text)
    funding_opportunity_goals = Column(Text)
    funding_opportunity_number = Column(Text)
    indirect_federal_sharing = Column(Numeric)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedAwardFinancialAssistance, self).__init__(**clean_kwargs)


class PublishedAwardProcurement(Base):
    """ Model for Published D1-Award (Procurement). """
    __tablename__ = 'published_award_procurement'

    published_award_procurement_id = Column(BigInteger, primary_key=True)
    detached_award_proc_unique = Column(Text)
    unique_award_key = Column(Text)
    submission_id = Column(Integer,
                           ForeignKey('submission.submission_id', ondelete='CASCADE',
                                      name='fk_published_award_procurement_submission_id'),
                           nullable=False, index=True)
    submission = relationship('Submission', uselist=False, cascade='delete')
    job_id = Column(Integer)
    row_number = Column(Integer)
    piid = Column(Text)
    awarding_sub_tier_agency_c = Column(Text)
    awarding_sub_tier_agency_n = Column(Text)
    awarding_agency_code = Column(Text)
    awarding_agency_name = Column(Text)
    parent_award_id = Column(Text)
    award_modification_amendme = Column(Text)
    type_of_contract_pricing = Column(Text)
    type_of_contract_pric_desc = Column(Text)
    contract_award_type = Column(Text)
    contract_award_type_desc = Column(Text)
    naics = Column(Text)
    naics_description = Column(Text)
    awardee_or_recipient_uniqu = Column(Text)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_unique_ide = Column(Text)
    award_description = Column(Text)
    place_of_performance_zip4a = Column(Text)
    place_of_performance_city = Column(Text)
    place_of_performance_congr = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_descrip = Column(Text)
    legal_entity_zip4 = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_country_name = Column(Text)
    period_of_performance_star = Column(Text)
    period_of_performance_curr = Column(Text)
    period_of_perf_potential_e = Column(Text)
    ordering_period_end_date = Column(Text)
    action_date = Column(Text)
    action_type = Column(Text)
    action_type_description = Column(Text)
    federal_action_obligation = Column(Numeric)
    current_total_value_award = Column(Text)
    potential_total_value_awar = Column(Text)
    funding_sub_tier_agency_co = Column(Text)
    funding_sub_tier_agency_na = Column(Text)
    funding_office_code = Column(Text)
    funding_office_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    referenced_idv_agency_iden = Column(Text)
    funding_agency_code = Column(Text)
    funding_agency_name = Column(Text)
    place_of_performance_locat = Column(Text)
    place_of_performance_state = Column(Text)
    place_of_perform_country_c = Column(Text)
    idv_type = Column(Text)
    idv_type_description = Column(Text)
    entity_doing_business_as_n = Column(Text)
    entity_phone_number = Column(Text)
    entity_fax_number = Column(Text)
    multiple_or_single_award_i = Column(Text)
    multiple_or_single_aw_desc = Column(Text)
    referenced_mult_or_single = Column(Text)
    referenced_mult_or_si_desc = Column(Text)
    type_of_idc = Column(Text)
    type_of_idc_description = Column(Text)
    a_76_fair_act_action = Column(Text)
    a_76_fair_act_action_desc = Column(Text)
    dod_claimant_program_code = Column(Text)
    dod_claimant_prog_cod_desc = Column(Text)
    clinger_cohen_act_planning = Column(Text)
    clinger_cohen_act_pla_desc = Column(Text)
    commercial_item_acquisitio = Column(Text)
    commercial_item_acqui_desc = Column(Text)
    commercial_item_test_progr = Column(Text)
    commercial_item_test_desc = Column(Text)
    consolidated_contract = Column(Text)
    consolidated_contract_desc = Column(Text)
    contingency_humanitarian_o = Column(Text)
    contingency_humanitar_desc = Column(Text)
    contract_bundling = Column(Text)
    contract_bundling_descrip = Column(Text)
    contract_financing = Column(Text)
    contract_financing_descrip = Column(Text)
    contracting_officers_deter = Column(Text)
    contracting_officers_desc = Column(Text)
    cost_accounting_standards = Column(Text)
    cost_accounting_stand_desc = Column(Text)
    cost_or_pricing_data = Column(Text)
    cost_or_pricing_data_desc = Column(Text)
    country_of_product_or_serv = Column(Text)
    country_of_product_or_desc = Column(Text)
    construction_wage_rate_req = Column(Text)
    construction_wage_rat_desc = Column(Text)
    evaluated_preference = Column(Text)
    evaluated_preference_desc = Column(Text)
    extent_competed = Column(Text)
    extent_compete_description = Column(Text)
    contract_opp_notice = Column(Text)
    contract_opp_notice_desc = Column(Text)
    foreign_funding = Column(Text)
    foreign_funding_desc = Column(Text)
    government_furnished_prope = Column(Text)
    government_furnished_desc = Column(Text)
    information_technology_com = Column(Text)
    information_technolog_desc = Column(Text)
    interagency_contracting_au = Column(Text)
    interagency_contract_desc = Column(Text)
    local_area_set_aside = Column(Text)
    local_area_set_aside_desc = Column(Text)
    major_program = Column(Text)
    purchase_card_as_payment_m = Column(Text)
    purchase_card_as_paym_desc = Column(Text)
    multi_year_contract = Column(Text)
    multi_year_contract_desc = Column(Text)
    national_interest_action = Column(Text)
    national_interest_desc = Column(Text)
    number_of_actions = Column(Text)
    number_of_offers_received = Column(Text)
    other_statutory_authority = Column(Text)
    performance_based_service = Column(Text)
    performance_based_se_desc = Column(Text)
    place_of_manufacture = Column(Text)
    place_of_manufacture_desc = Column(Text)
    price_evaluation_adjustmen = Column(Text)
    product_or_service_code = Column(Text)
    product_or_service_co_desc = Column(Text)
    program_acronym = Column(Text)
    other_than_full_and_open_c = Column(Text)
    other_than_full_and_o_desc = Column(Text)
    recovered_materials_sustai = Column(Text)
    recovered_materials_s_desc = Column(Text)
    research = Column(Text)
    research_description = Column(Text)
    sea_transportation = Column(Text)
    sea_transportation_desc = Column(Text)
    labor_standards = Column(Text)
    labor_standards_descrip = Column(Text)
    small_business_competitive = Column(Boolean)
    solicitation_identifier = Column(Text)
    solicitation_date = Column(Text)
    solicitation_procedures = Column(Text)
    solicitation_procedur_desc = Column(Text)
    fair_opportunity_limited_s = Column(Text)
    fair_opportunity_limi_desc = Column(Text)
    subcontracting_plan = Column(Text)
    subcontracting_plan_desc = Column(Text)
    program_system_or_equipmen = Column(Text)
    program_system_or_equ_desc = Column(Text)
    type_set_aside = Column(Text)
    type_set_aside_description = Column(Text)
    epa_designated_product = Column(Text)
    epa_designated_produc_desc = Column(Text)
    materials_supplies_article = Column(Text)
    materials_supplies_descrip = Column(Text)
    transaction_number = Column(Text)
    sam_exception = Column(Text)
    sam_exception_description = Column(Text)
    city_local_government = Column(Boolean)
    county_local_government = Column(Boolean)
    inter_municipal_local_gove = Column(Boolean)
    local_government_owned = Column(Boolean)
    municipality_local_governm = Column(Boolean)
    school_district_local_gove = Column(Boolean)
    township_local_government = Column(Boolean)
    us_state_government = Column(Boolean)
    us_federal_government = Column(Boolean)
    federal_agency = Column(Boolean)
    federally_funded_research = Column(Boolean)
    us_tribal_government = Column(Boolean)
    foreign_government = Column(Boolean)
    community_developed_corpor = Column(Boolean)
    labor_surplus_area_firm = Column(Boolean)
    corporate_entity_not_tax_e = Column(Boolean)
    corporate_entity_tax_exemp = Column(Boolean)
    partnership_or_limited_lia = Column(Boolean)
    sole_proprietorship = Column(Boolean)
    small_agricultural_coopera = Column(Boolean)
    international_organization = Column(Boolean)
    us_government_entity = Column(Boolean)
    emerging_small_business = Column(Boolean)
    c8a_program_participant = Column(Boolean)
    sba_certified_8_a_joint_ve = Column(Boolean)
    dot_certified_disadvantage = Column(Boolean)
    self_certified_small_disad = Column(Boolean)
    historically_underutilized = Column(Boolean)
    small_disadvantaged_busine = Column(Boolean)
    the_ability_one_program = Column(Boolean)
    historically_black_college = Column(Boolean)
    c1862_land_grant_college = Column(Boolean)
    c1890_land_grant_college = Column(Boolean)
    c1994_land_grant_college = Column(Boolean)
    minority_institution = Column(Boolean)
    private_university_or_coll = Column(Boolean)
    school_of_forestry = Column(Boolean)
    state_controlled_instituti = Column(Boolean)
    tribal_college = Column(Boolean)
    veterinary_college = Column(Boolean)
    educational_institution = Column(Boolean)
    alaskan_native_servicing_i = Column(Boolean)
    community_development_corp = Column(Boolean)
    native_hawaiian_servicing = Column(Boolean)
    domestic_shelter = Column(Boolean)
    manufacturer_of_goods = Column(Boolean)
    hospital_flag = Column(Boolean)
    veterinary_hospital = Column(Boolean)
    hispanic_servicing_institu = Column(Boolean)
    foundation = Column(Boolean)
    woman_owned_business = Column(Boolean)
    minority_owned_business = Column(Boolean)
    women_owned_small_business = Column(Boolean)
    economically_disadvantaged = Column(Boolean)
    joint_venture_women_owned = Column(Boolean)
    joint_venture_economically = Column(Boolean)
    veteran_owned_business = Column(Boolean)
    service_disabled_veteran_o = Column(Boolean)
    contracts = Column(Boolean)
    grants = Column(Boolean)
    receives_contracts_and_gra = Column(Boolean)
    airport_authority = Column(Boolean)
    council_of_governments = Column(Boolean)
    housing_authorities_public = Column(Boolean)
    interstate_entity = Column(Boolean)
    planning_commission = Column(Boolean)
    port_authority = Column(Boolean)
    transit_authority = Column(Boolean)
    subchapter_s_corporation = Column(Boolean)
    limited_liability_corporat = Column(Boolean)
    foreign_owned_and_located = Column(Boolean)
    american_indian_owned_busi = Column(Boolean)
    alaskan_native_owned_corpo = Column(Boolean)
    indian_tribe_federally_rec = Column(Boolean)
    native_hawaiian_owned_busi = Column(Boolean)
    tribally_owned_business = Column(Boolean)
    asian_pacific_american_own = Column(Boolean)
    black_american_owned_busin = Column(Boolean)
    hispanic_american_owned_bu = Column(Boolean)
    native_american_owned_busi = Column(Boolean)
    subcontinent_asian_asian_i = Column(Boolean)
    other_minority_owned_busin = Column(Boolean)
    for_profit_organization = Column(Boolean)
    nonprofit_organization = Column(Boolean)
    other_not_for_profit_organ = Column(Boolean)
    us_local_government = Column(Boolean)
    referenced_idv_modificatio = Column(Text)
    undefinitized_action = Column(Text)
    undefinitized_action_desc = Column(Text)
    domestic_or_foreign_entity = Column(Text)
    domestic_or_foreign_e_desc = Column(Text)
    referenced_idv_type = Column(Text)
    referenced_idv_type_desc = Column(Text)
    referenced_idv_agency_name = Column(Text)
    award_or_idv_flag = Column(Text)
    place_of_perform_country_n = Column(Text)
    place_of_perform_state_nam = Column(Text)
    place_of_perform_county_na = Column(Text)
    base_exercised_options_val = Column(Text)
    base_and_all_options_value = Column(Text)
    cage_code = Column(Text)
    inherently_government_func = Column(Text)
    inherently_government_desc = Column(Text)
    organizational_type = Column(Text)
    number_of_employees = Column(Text)
    annual_revenue = Column(Text)
    total_obligated_amount = Column(Text)
    last_modified = Column(Text)
    additional_reporting = Column(Text)
    awardee_or_recipient_uei = Column(Text)
    ultimate_parent_uei = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedAwardProcurement, self).__init__(**clean_kwargs)


class DetachedAwardProcurement(Base):
    """Model for D1-Award (Procurement)."""
    __tablename__ = "detached_award_procurement"
    detached_award_procurement_id = Column(BigInteger, primary_key=True)
    detached_award_proc_unique = Column(Text, unique=True, nullable=False)
    piid = Column(Text, index=True)
    agency_id = Column(Text)
    awarding_sub_tier_agency_c = Column(Text, index=True)
    awarding_sub_tier_agency_n = Column(Text)
    awarding_agency_code = Column(Text, index=True)
    awarding_agency_name = Column(Text)
    parent_award_id = Column(Text, index=True)
    award_modification_amendme = Column(Text)
    type_of_contract_pricing = Column(Text)
    type_of_contract_pric_desc = Column(Text)
    contract_award_type = Column(Text)
    contract_award_type_desc = Column(Text)
    naics = Column(Text)
    naics_description = Column(Text)
    awardee_or_recipient_uniqu = Column(Text, index=True)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_unique_ide = Column(Text, index=True)
    award_description = Column(Text)
    place_of_performance_zip4a = Column(Text)
    place_of_performance_zip5 = Column(Text)
    place_of_perform_zip_last4 = Column(Text)
    place_of_perform_city_name = Column(Text)
    place_of_perform_county_co = Column(Text)
    place_of_perform_county_na = Column(Text)
    place_of_performance_congr = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_county_code = Column(Text)
    legal_entity_county_name = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_descrip = Column(Text)
    legal_entity_zip4 = Column(Text)
    legal_entity_zip5 = Column(Text)
    legal_entity_zip_last4 = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_country_name = Column(Text)
    period_of_performance_star = Column(Text)
    period_of_performance_curr = Column(Text)
    period_of_perf_potential_e = Column(Text)
    ordering_period_end_date = Column(Text)
    action_date = Column(Text, index=True)
    action_type = Column(Text)
    action_type_description = Column(Text)
    federal_action_obligation = Column(Numeric)
    current_total_value_award = Column(Text)
    potential_total_value_awar = Column(Text)
    total_obligated_amount = Column(Text)
    base_exercised_options_val = Column(Text)
    base_and_all_options_value = Column(Text)
    funding_sub_tier_agency_co = Column(Text, index=True)
    funding_sub_tier_agency_na = Column(Text)
    funding_office_code = Column(Text)
    funding_office_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    referenced_idv_agency_iden = Column(Text)
    referenced_idv_agency_desc = Column(Text)
    funding_agency_code = Column(Text, index=True)
    funding_agency_name = Column(Text)
    place_of_performance_locat = Column(Text)
    place_of_performance_state = Column(Text)
    place_of_perfor_state_desc = Column(Text)
    place_of_perform_country_c = Column(Text)
    place_of_perf_country_desc = Column(Text)
    idv_type = Column(Text)
    idv_type_description = Column(Text)
    referenced_idv_type = Column(Text)
    referenced_idv_type_desc = Column(Text)
    vendor_doing_as_business_n = Column(Text)
    vendor_phone_number = Column(Text)
    vendor_fax_number = Column(Text)
    multiple_or_single_award_i = Column(Text)
    multiple_or_single_aw_desc = Column(Text)
    referenced_mult_or_single = Column(Text)
    referenced_mult_or_si_desc = Column(Text)
    type_of_idc = Column(Text)
    type_of_idc_description = Column(Text)
    a_76_fair_act_action = Column(Text)
    a_76_fair_act_action_desc = Column(Text)
    dod_claimant_program_code = Column(Text)
    dod_claimant_prog_cod_desc = Column(Text)
    clinger_cohen_act_planning = Column(Text)
    clinger_cohen_act_pla_desc = Column(Text)
    commercial_item_acquisitio = Column(Text)
    commercial_item_acqui_desc = Column(Text)
    commercial_item_test_progr = Column(Text)
    commercial_item_test_desc = Column(Text)
    consolidated_contract = Column(Text)
    consolidated_contract_desc = Column(Text)
    contingency_humanitarian_o = Column(Text)
    contingency_humanitar_desc = Column(Text)
    contract_bundling = Column(Text)
    contract_bundling_descrip = Column(Text)
    contract_financing = Column(Text)
    contract_financing_descrip = Column(Text)
    contracting_officers_deter = Column(Text)
    contracting_officers_desc = Column(Text)
    cost_accounting_standards = Column(Text)
    cost_accounting_stand_desc = Column(Text)
    cost_or_pricing_data = Column(Text)
    cost_or_pricing_data_desc = Column(Text)
    country_of_product_or_serv = Column(Text)
    country_of_product_or_desc = Column(Text)
    construction_wage_rate_req = Column(Text)
    construction_wage_rat_desc = Column(Text)
    evaluated_preference = Column(Text)
    evaluated_preference_desc = Column(Text)
    extent_competed = Column(Text)
    extent_compete_description = Column(Text)
    fed_biz_opps = Column(Text)
    fed_biz_opps_description = Column(Text)
    foreign_funding = Column(Text)
    foreign_funding_desc = Column(Text)
    government_furnished_prope = Column(Text)
    government_furnished_desc = Column(Text)
    information_technology_com = Column(Text)
    information_technolog_desc = Column(Text)
    interagency_contracting_au = Column(Text)
    interagency_contract_desc = Column(Text)
    local_area_set_aside = Column(Text)
    local_area_set_aside_desc = Column(Text)
    major_program = Column(Text)
    purchase_card_as_payment_m = Column(Text)
    purchase_card_as_paym_desc = Column(Text)
    multi_year_contract = Column(Text)
    multi_year_contract_desc = Column(Text)
    national_interest_action = Column(Text)
    national_interest_desc = Column(Text)
    number_of_actions = Column(Text)
    number_of_offers_received = Column(Text)
    other_statutory_authority = Column(Text)
    performance_based_service = Column(Text)
    performance_based_se_desc = Column(Text)
    place_of_manufacture = Column(Text)
    place_of_manufacture_desc = Column(Text)
    price_evaluation_adjustmen = Column(Text)
    product_or_service_code = Column(Text)
    product_or_service_co_desc = Column(Text)
    program_acronym = Column(Text)
    other_than_full_and_open_c = Column(Text)
    other_than_full_and_o_desc = Column(Text)
    recovered_materials_sustai = Column(Text)
    recovered_materials_s_desc = Column(Text)
    research = Column(Text)
    research_description = Column(Text)
    sea_transportation = Column(Text)
    sea_transportation_desc = Column(Text)
    labor_standards = Column(Text)
    labor_standards_descrip = Column(Text)
    small_business_competitive = Column(Boolean)
    solicitation_identifier = Column(Text)
    solicitation_date = Column(Text)
    solicitation_procedures = Column(Text)
    solicitation_procedur_desc = Column(Text)
    fair_opportunity_limited_s = Column(Text)
    fair_opportunity_limi_desc = Column(Text)
    subcontracting_plan = Column(Text)
    subcontracting_plan_desc = Column(Text)
    program_system_or_equipmen = Column(Text)
    program_system_or_equ_desc = Column(Text)
    type_set_aside = Column(Text)
    type_set_aside_description = Column(Text)
    epa_designated_product = Column(Text)
    epa_designated_produc_desc = Column(Text)
    materials_supplies_article = Column(Text)
    materials_supplies_descrip = Column(Text)
    transaction_number = Column(Text)
    sam_exception = Column(Text)
    sam_exception_description = Column(Text)
    city_local_government = Column(Boolean)
    county_local_government = Column(Boolean)
    inter_municipal_local_gove = Column(Boolean)
    local_government_owned = Column(Boolean)
    municipality_local_governm = Column(Boolean)
    school_district_local_gove = Column(Boolean)
    township_local_government = Column(Boolean)
    us_state_government = Column(Boolean)
    us_federal_government = Column(Boolean)
    federal_agency = Column(Boolean)
    federally_funded_research = Column(Boolean)
    us_tribal_government = Column(Boolean)
    foreign_government = Column(Boolean)
    community_developed_corpor = Column(Boolean)
    labor_surplus_area_firm = Column(Boolean)
    corporate_entity_not_tax_e = Column(Boolean)
    corporate_entity_tax_exemp = Column(Boolean)
    partnership_or_limited_lia = Column(Boolean)
    sole_proprietorship = Column(Boolean)
    small_agricultural_coopera = Column(Boolean)
    international_organization = Column(Boolean)
    us_government_entity = Column(Boolean)
    emerging_small_business = Column(Boolean)
    c8a_program_participant = Column(Boolean)
    sba_certified_8_a_joint_ve = Column(Boolean)
    dot_certified_disadvantage = Column(Boolean)
    self_certified_small_disad = Column(Boolean)
    historically_underutilized = Column(Boolean)
    small_disadvantaged_busine = Column(Boolean)
    the_ability_one_program = Column(Boolean)
    historically_black_college = Column(Boolean)
    c1862_land_grant_college = Column(Boolean)
    c1890_land_grant_college = Column(Boolean)
    c1994_land_grant_college = Column(Boolean)
    minority_institution = Column(Boolean)
    private_university_or_coll = Column(Boolean)
    school_of_forestry = Column(Boolean)
    state_controlled_instituti = Column(Boolean)
    tribal_college = Column(Boolean)
    veterinary_college = Column(Boolean)
    educational_institution = Column(Boolean)
    alaskan_native_servicing_i = Column(Boolean)
    community_development_corp = Column(Boolean)
    native_hawaiian_servicing = Column(Boolean)
    domestic_shelter = Column(Boolean)
    manufacturer_of_goods = Column(Boolean)
    hospital_flag = Column(Boolean)
    veterinary_hospital = Column(Boolean)
    hispanic_servicing_institu = Column(Boolean)
    foundation = Column(Boolean)
    woman_owned_business = Column(Boolean)
    minority_owned_business = Column(Boolean)
    women_owned_small_business = Column(Boolean)
    economically_disadvantaged = Column(Boolean)
    joint_venture_women_owned = Column(Boolean)
    joint_venture_economically = Column(Boolean)
    veteran_owned_business = Column(Boolean)
    service_disabled_veteran_o = Column(Boolean)
    contracts = Column(Boolean)
    grants = Column(Boolean)
    receives_contracts_and_gra = Column(Boolean)
    airport_authority = Column(Boolean)
    council_of_governments = Column(Boolean)
    housing_authorities_public = Column(Boolean)
    interstate_entity = Column(Boolean)
    planning_commission = Column(Boolean)
    port_authority = Column(Boolean)
    transit_authority = Column(Boolean)
    subchapter_s_corporation = Column(Boolean)
    limited_liability_corporat = Column(Boolean)
    foreign_owned_and_located = Column(Boolean)
    american_indian_owned_busi = Column(Boolean)
    alaskan_native_owned_corpo = Column(Boolean)
    indian_tribe_federally_rec = Column(Boolean)
    native_hawaiian_owned_busi = Column(Boolean)
    tribally_owned_business = Column(Boolean)
    asian_pacific_american_own = Column(Boolean)
    black_american_owned_busin = Column(Boolean)
    hispanic_american_owned_bu = Column(Boolean)
    native_american_owned_busi = Column(Boolean)
    subcontinent_asian_asian_i = Column(Boolean)
    other_minority_owned_busin = Column(Boolean)
    for_profit_organization = Column(Boolean)
    nonprofit_organization = Column(Boolean)
    other_not_for_profit_organ = Column(Boolean)
    us_local_government = Column(Boolean)
    referenced_idv_modificatio = Column(Text)
    undefinitized_action = Column(Text)
    undefinitized_action_desc = Column(Text)
    domestic_or_foreign_entity = Column(Text)
    domestic_or_foreign_e_desc = Column(Text)
    annual_revenue = Column(Text)
    division_name = Column(Text)
    division_number_or_office = Column(Text)
    number_of_employees = Column(Text)
    vendor_alternate_name = Column(Text)
    vendor_alternate_site_code = Column(Text)
    vendor_enabled = Column(Text)
    vendor_legal_org_name = Column(Text)
    vendor_location_disabled_f = Column(Text)
    vendor_site_code = Column(Text)
    pulled_from = Column(Text, index=True)
    last_modified = Column(Text)
    initial_report_date = Column(Text)
    referenced_idv_agency_name = Column(Text)
    award_or_idv_flag = Column(Text)
    place_of_perform_country_n = Column(Text)
    place_of_perform_state_nam = Column(Text)
    cage_code = Column(Text)
    inherently_government_func = Column(Text)
    inherently_government_desc = Column(Text)
    organizational_type = Column(Text)
    business_categories = Column(ARRAY(Text))
    ignore_updated_at = False
    unique_award_key = Column(Text, index=True)
    high_comp_officer1_full_na = Column(Text)
    high_comp_officer1_amount = Column(Text)
    high_comp_officer2_full_na = Column(Text)
    high_comp_officer2_amount = Column(Text)
    high_comp_officer3_full_na = Column(Text)
    high_comp_officer3_amount = Column(Text)
    high_comp_officer4_full_na = Column(Text)
    high_comp_officer4_amount = Column(Text)
    high_comp_officer5_full_na = Column(Text)
    high_comp_officer5_amount = Column(Text)
    additional_reporting = Column(Text)
    awardee_or_recipient_uei = Column(Text, index=True)
    ultimate_parent_uei = Column(Text, index=True)
    entity_data_source = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(DetachedAwardProcurement, self).__init__(**clean_kwargs)


Index("ix_dap_piid_upper", func.upper(DetachedAwardProcurement.piid))
Index("ix_dap_parent_award_id_upper", func.upper(DetachedAwardProcurement.parent_award_id))
Index("ix_dap_awarding_sub_tier_agency_c_upper", func.upper(DetachedAwardProcurement.awarding_sub_tier_agency_c))
Index("ix_dap_awardee_or_recipient_uei_upper", func.upper(DetachedAwardProcurement.awardee_or_recipient_uei))


class FABS(Base):
    """Model for FABS submission."""
    __tablename__ = "fabs"

    fabs_id = Column(BigInteger, primary_key=True)
    afa_generated_unique = Column(Text, index=True, nullable=False)
    submission_id = Column(Integer,
                           ForeignKey("submission.submission_id", ondelete="CASCADE",
                                      name="fk_fabs_submission_id"),
                           nullable=False, index=True)
    submission = relationship("Submission", uselist=False, cascade="delete")
    job_id = Column(Integer, nullable=False, index=True)
    row_number = Column(Integer, nullable=False, index=True)
    action_date = Column(Text, index=True)
    action_type = Column(Text, index=True)
    assistance_type = Column(Text, index=True)
    award_description = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    awardee_or_recipient_uniqu = Column(Text, index=True)
    awarding_office_code = Column(Text)
    awarding_sub_tier_agency_c = Column(Text, index=True)
    award_modification_amendme = Column(Text)
    business_funds_indicator = Column(Text)
    business_types = Column(Text)
    cfda_number = Column(Text, index=True)
    correction_delete_indicatr = Column(Text, index=True)
    face_value_loan_guarantee = Column(Numeric)
    fain = Column(Text, index=True)
    federal_action_obligation = Column(Numeric, index=True)
    funding_office_code = Column(Text, index=True)
    funding_opportunity_goals = Column(Text, index=True)
    funding_opportunity_number = Column(Text, index=True)
    funding_sub_tier_agency_co = Column(Text, index=True)
    indirect_federal_sharing = Column(Numeric, index=True)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_congressional = Column(Text, index=True)
    legal_entity_country_code = Column(Text)
    legal_entity_foreign_city = Column(Text)
    legal_entity_foreign_posta = Column(Text)
    legal_entity_foreign_provi = Column(Text)
    legal_entity_zip5 = Column(Text, index=True)
    legal_entity_zip_last4 = Column(Text, index=True)
    non_federal_funding_amount = Column(Numeric)
    original_loan_subsidy_cost = Column(Numeric)
    period_of_performance_curr = Column(Text, index=True)
    period_of_performance_star = Column(Text, index=True)
    place_of_performance_code = Column(Text, index=True)
    place_of_performance_congr = Column(Text)
    place_of_perform_country_c = Column(Text, index=True)
    place_of_performance_forei = Column(Text)
    place_of_performance_zip4a = Column(Text)
    record_type = Column(Integer, index=True)
    sai_number = Column(Text)
    uri = Column(Text, index=True)
    is_valid = Column(Boolean, nullable=False, default=False, server_default="False")
    unique_award_key = Column(Text, index=True)
    uei = Column(Text, index=True)
    ultimate_parent_uei = Column(Text)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(FABS, self).__init__(**clean_kwargs)


Index("ix_fabs_afa_generated_unique_upper", func.upper(FABS.afa_generated_unique))
Index("ix_fabs_uei_upper", func.upper(FABS.uei))


class PublishedFABS(Base):
    """Model for published FABS."""
    __tablename__ = "published_fabs"

    published_fabs_id = Column(BigInteger, primary_key=True)
    afa_generated_unique = Column(Text, index=True, nullable=False)
    action_date = Column(Text, index=True)
    action_type = Column(Text, index=True)
    action_type_description = Column(Text)
    assistance_type = Column(Text, index=True)
    assistance_type_desc = Column(Text)
    award_description = Column(Text)
    awardee_or_recipient_legal = Column(Text)
    awardee_or_recipient_uniqu = Column(Text, index=True)
    awarding_agency_code = Column(Text, index=True)
    awarding_agency_name = Column(Text)
    awarding_office_code = Column(Text)
    awarding_office_name = Column(Text)
    awarding_sub_tier_agency_c = Column(Text, index=True)
    awarding_sub_tier_agency_n = Column(Text)
    award_modification_amendme = Column(Text)
    business_funds_indicator = Column(Text)
    business_funds_ind_desc = Column(Text)
    business_types = Column(Text)
    business_types_desc = Column(Text)
    business_categories = Column(ARRAY(Text))
    cfda_number = Column(Text, index=True)
    cfda_title = Column(Text)
    correction_delete_indicatr = Column(Text, index=True)
    correction_delete_ind_desc = Column(Text)
    face_value_loan_guarantee = Column(Numeric)
    fain = Column(Text, index=True)
    federal_action_obligation = Column(Numeric)
    fiscal_year_and_quarter_co = Column(Text)
    funding_agency_name = Column(Text)
    funding_agency_code = Column(Text, index=True)
    funding_office_code = Column(Text, index=True)
    funding_office_name = Column(Text)
    funding_opportunity_goals = Column(Text)
    funding_opportunity_number = Column(Text)
    funding_sub_tier_agency_co = Column(Text, index=True)
    funding_sub_tier_agency_na = Column(Text)
    indirect_federal_sharing = Column(Numeric)
    is_active = Column(Boolean, default=False, nullable=False, server_default="False", index=True)
    is_historical = Column(Boolean)
    legal_entity_address_line1 = Column(Text)
    legal_entity_address_line2 = Column(Text)
    legal_entity_address_line3 = Column(Text)
    legal_entity_city_name = Column(Text)
    legal_entity_city_code = Column(Text)
    legal_entity_congressional = Column(Text)
    legal_entity_country_code = Column(Text)
    legal_entity_county_code = Column(Text)
    legal_entity_county_name = Column(Text)
    legal_entity_foreign_city = Column(Text)
    legal_entity_foreign_posta = Column(Text)
    legal_entity_foreign_provi = Column(Text)
    legal_entity_foreign_descr = Column(Text)
    legal_entity_state_code = Column(Text)
    legal_entity_state_name = Column(Text)
    legal_entity_zip5 = Column(Text)
    legal_entity_zip_last4 = Column(Text)
    modified_at = Column(DateTime)
    non_federal_funding_amount = Column(Numeric)
    original_loan_subsidy_cost = Column(Numeric)
    period_of_performance_curr = Column(Text, index=True)
    period_of_performance_star = Column(Text, index=True)
    place_of_performance_city = Column(Text)
    place_of_performance_code = Column(Text)
    place_of_performance_congr = Column(Text, index=True)
    place_of_perform_country_c = Column(Text, index=True)
    place_of_perform_county_na = Column(Text)
    place_of_performance_forei = Column(Text)
    place_of_perfor_state_code = Column(Text)
    place_of_perform_state_nam = Column(Text)
    place_of_performance_zip4a = Column(Text)
    place_of_performance_zip5 = Column(Text)
    place_of_perform_zip_last4 = Column(Text)
    place_of_performance_scope = Column(Text)
    record_type = Column(Integer, index=True)
    record_type_description = Column(Text)
    sai_number = Column(Text)
    total_funding_amount = Column(Text)
    ultimate_parent_legal_enti = Column(Text)
    ultimate_parent_unique_ide = Column(Text, index=True)
    uri = Column(Text, index=True)
    place_of_perform_county_co = Column(Text)
    place_of_perform_country_n = Column(Text)
    legal_entity_country_name = Column(Text)
    submission_id = Column(Numeric, index=True)
    ignore_updated_at = False
    unique_award_key = Column(Text, index=True)
    high_comp_officer1_full_na = Column(Text)
    high_comp_officer1_amount = Column(Text)
    high_comp_officer2_full_na = Column(Text)
    high_comp_officer2_amount = Column(Text)
    high_comp_officer3_full_na = Column(Text)
    high_comp_officer3_amount = Column(Text)
    high_comp_officer4_full_na = Column(Text)
    high_comp_officer4_amount = Column(Text)
    high_comp_officer5_full_na = Column(Text)
    high_comp_officer5_amount = Column(Text)
    uei = Column(Text, index=True)
    ultimate_parent_uei = Column(Text, index=True)

    def __init__(self, **kwargs):
        # broker is set up to ignore extra columns in submitted data
        # so get rid of any extraneous kwargs before instantiating
        clean_kwargs = {k: v for k, v in kwargs.items() if hasattr(self, k)}
        super(PublishedFABS, self).__init__(**clean_kwargs)


Index(
    'ix_published_fabs_is_active',
    PublishedFABS.is_active,
    postgresql_where=(PublishedFABS.is_active.is_(True)))

Index("ix_published_fabs_fain_awarding_sub_tier_is_active",
      PublishedFABS.fain,
      PublishedFABS.awarding_sub_tier_agency_c,
      PublishedFABS.is_active,
      unique=False)

Index("ix_published_fabs_fain_awarding_subtier_upper",
      func.upper(PublishedFABS.fain),
      func.upper(PublishedFABS.awarding_sub_tier_agency_c),
      unique=False)

Index("ix_published_fabs_uri_awarding_sub_tier_is_active",
      PublishedFABS.uri,
      PublishedFABS.awarding_sub_tier_agency_c,
      PublishedFABS.is_active,
      unique=False)

Index("ix_published_fabs_uri_awarding_subtier_upper",
      func.upper(PublishedFABS.uri),
      func.upper(PublishedFABS.awarding_sub_tier_agency_c),
      unique=False)

Index("ix_published_fabs_fain_upper", func.upper(PublishedFABS.fain))
Index("ix_published_fabs_uri_upper", func.upper(PublishedFABS.uri))
Index("ix_published_fabs_awarding_subtier_c_upper", func.upper(PublishedFABS.awarding_sub_tier_agency_c))
Index("ix_published_fabs_afa_generated_unique_upper", func.upper(PublishedFABS.afa_generated_unique))
Index("ix_published_fabs_uei_upper", func.upper(PublishedFABS.uei))
