class DelphiTables:
    def __init__(self, bind):
        self.bind = bind

    def create(self):
        tables = [
            self.decibel_labels_table,
            self.projects_table,
            self.project_artists_table,
            self.user_projects_table,
            self.user_labels_table,
            self.campaign_sub_category_table,
            self.campaign_category_table,
            self.campaign_platforms_table,
            self.campaign_objective_table,
            self.campaign_provider_table,
            self.campaigns_table,
            self.phases_table,
            self.phase_campaigns_table,
            self.project_campaigns_table,
            self.linkfire_campaign_table,
            self.linkfire_project_table
        ]
        for t in tables:
            self.bind.execute(t())

        self.bind.execute(
            self.create_indexes()
        )

    def reindex(self):
        self.bind.execute(
            """
                REINDEX TABLE projects;
                REINDEX TABLE campaigns;
                REINDEX TABLE project_artists;
                REINDEX TABLE project_campaigns;
                REINDEX TABLE user_projects;
                REINDEX TABLE user_labels;
                REINDEX TABLE decibel_labels;
                REINDEX TABLE phases;
                REINDEX TABLE phase_campaigns;
                REINDEX TABLE campaign_platforms;
                REINDEX TABLE campaign_sub_category;
                REINDEX TABLE campaign_provider;
                REINDEX TABLE campaign_objective;
                REINDEX TABLE campaign_category;
                REINDEX TABLE linkfire_link_campaign;
                REINDEX TABLE linkfire_link_project;
            """
        )


    def drop(self):
        self.bind.execute(
            self.drop_indexes()
        )
        self.bind.execute(
            """
                DROP TABLE IF EXISTS projects CASCADE;
                DROP TABLE IF EXISTS campaigns CASCADE;
                DROP TABLE IF EXISTS project_artists CASCADE;
                DROP TABLE IF EXISTS project_campaigns CASCADE;
                DROP TABLE IF EXISTS user_projects CASCADE;
                DROP TABLE IF EXISTS user_labels CASCADE;
                DROP TABLE IF EXISTS decibel_labels CASCADE;
                DROP TABLE IF EXISTS phases CASCADE;
                DROP TABLE IF EXISTS phase_campaigns CASCADE;
                DROP TABLE IF EXISTS campaign_platforms CASCADE;
                DROP TABLE IF EXISTS campaign_sub_category CASCADE;
                DROP TABLE IF EXISTS campaign_provider CASCADE;
                DROP TABLE IF EXISTS campaign_objective CASCADE;
                DROP TABLE IF EXISTS campaign_category CASCADE;
                DROP TABLE IF EXISTS linkfire_link_campaign CASCADE;
                DROP TABLE IF EXISTS linkfire_link_project CASCADE;
            """
        )

    def create_indexes(self):
        return """
            CREATE INDEX ix_projects_id ON projects using btree(id);
            CREATE INDEX ix_projects_gras_id ON projects using btree(gras_id);
            CREATE INDEX ix_projects_decibel_label_id ON projects using btree(decibel_label_id);

            CREATE INDEX ix_campaigns_id On campaigns using btree(id);
            CREATE INDEX ix_campaigns_provider_id On campaigns using btree(provider_id);
            CREATE INDEX ix_campaigns_objective_id On campaigns using btree(objective_id);
            CREATE INDEX ix_campaigns_decibel_label_id On campaigns using btree(decibel_label_id);
            CREATE INDEX ix_campaigns_category_id On campaigns using btree(category_id);
            CREATE INDEX ix_campaigns_sub_category_id On campaigns using btree(sub_category_id);

            CREATE INDEX ix_project_artists_project_id ON project_artists using btree(project_id);
            CREATE INDEX ix_project_artists_artist_id ON project_artists using btree(artist_id);

            CREATE INDEX ix_project_campaigns_project_id ON project_campaigns using btree(project_id);
            CREATE INDEX ix_project_campaigns_campaign_id ON project_campaigns using btree(campaign_id);

            CREATE INDEX ix_user_projects_project_id ON user_projects using btree(project_id);
            CREATE INDEX ix_user_projects_user_id ON user_projects using btree(user_id);

            CREATE INDEX ix_user_labels_user_id ON user_labels using btree(user_id);
            CREATE INDEX ix_user_labels_decibel_label_id ON user_labels using btree(decibel_label_id);

            CREATE INDEX ix_decibel_labels_id ON decibel_labels using btree(id);
            CREATE INDEX ix_decibel_labels_rep_owner_key ON decibel_labels using btree(rep_owner_key);

            CREATE INDEX ix_phases_id ON phases using btree(id);
            CREATE INDEX ix_phases_project_id ON phases using btree(project_id);

            CREATE INDEX ix_phase_campaigns_phase_id ON phase_campaigns using btree(phase_id);
            CREATE INDEX ix_phase_campaigns_campaign_id ON phase_campaigns using btree(campaign_id);

            CREATE INDEX ix_campaign_platforms_campaign_id ON campaign_platforms using btree(campaign_id);

            CREATE INDEX ix_campaign_sub_category_id ON campaign_sub_category using btree(id);
            CREATE INDEX ix_campaign_provider_id ON campaign_provider using btree(id);
            CREATE INDEX ix_campaign_objective_id ON campaign_objective using btree(id);
            CREATE INDEX ix_campaign_category_id ON campaign_category using btree(id);

            CREATE INDEX IF NOT EXISTS ix_linkfire_link_campaign_campaign_id ON linkfire_link_campaign using btree(campaign_id);
            CREATE INDEX IF NOT EXISTS ix_linkfire_link_campaign_link_id ON linkfire_link_campaign using btree(link_id);

            CREATE INDEX IF NOT EXISTS ix_linkfire_link_project_project_id ON linkfire_link_project using btree(project_id);
            CREATE INDEX IF NOT EXISTS ix_linkfire_link_project_link_id ON linkfire_link_project using btree(link_id);
        """

    def drop_indexes(self):
        return """
            DROP INDEX IF EXISTS ix_projects_id;
            DROP INDEX IF EXISTS ix_projects_decibel_label_id;
            DROP INDEX IF EXISTS ix_campaigns_id;
            DROP INDEX IF EXISTS ix_campaigns_provider_id;
            DROP INDEX IF EXISTS ix_campaigns_objective_id;
            DROP INDEX IF EXISTS ix_campaigns_decibel_label_id;
            DROP INDEX IF EXISTS ix_campaigns_category_id;
            DROP INDEX IF EXISTS ix_campaigns_sub_category_id;
            DROP INDEX IF EXISTS ix_project_artists_project_id;
            DROP INDEX IF EXISTS ix_project_artists_artist_id;
            DROP INDEX IF EXISTS ix_project_campaigns_project_id;
            DROP INDEX IF EXISTS ix_project_campaigns_campaign_id;
            DROP INDEX IF EXISTS ix_user_projects_project_id;
            DROP INDEX IF EXISTS ix_user_projects_user_id;
            DROP INDEX IF EXISTS ix_user_labels_user_id;
            DROP INDEX IF EXISTS ix_user_labels_decibel_label_id;
            DROP INDEX IF EXISTS ix_decibel_labels_id;
            DROP INDEX IF EXISTS ix_phases_id;
            DROP INDEX IF EXISTS ix_phases_project_id;
            DROP INDEX IF EXISTS ix_phase_campaigns_phase_id;
            DROP INDEX IF EXISTS ix_phase_campaigns_campaign_id;
            DROP INDEX IF EXISTS ix_campaign_platforms_campaign_id;
            DROP INDEX IF EXISTS ix_campaign_sub_category_id;
            DROP INDEX IF EXISTS ix_campaign_provider_id;
            DROP INDEX IF EXISTS ix_campaign_objective_id;
            DROP INDEX IF EXISTS ix_campaign_category_id;
            DROP INDEX IF EXISTS ix_projects_decibel_label_id;
            DROP INDEX IF EXISTS ix_decibel_labels_rep_owner_key;
        """

    def projects_table(self):
        return """
            CREATE TABLE projects (
                id varchar,
                gras_id varchar,
                name varchar,
                budget numeric(12,2),
                decibel_label_id int4,
                end_date date,
                start_date date,
                PRIMARY KEY (id)
            );
        """

    def campaigns_table(self):
        return """
            CREATE TABLE campaigns (
                id	varchar,
                name varchar,
                budget numeric,
                spend numeric,
                provider_id	int4,
                start_date date,
                end_date date,
                is_pending bool,
                objective_id int4,
                decibel_label_id int4,
                category_id	int2,
                sub_category_id	int2,
                PRIMARY KEY (id)
            );
        """

    def project_artists_table(self):
        return """
            CREATE TABLE project_artists (
                project_id varchar,
                artist_id varchar
            );
        """

    def project_campaigns_table(self):
        return """
            CREATE TABLE project_campaigns (
                project_id varchar,
                campaign_id varchar
            );
        """

    def user_projects_table(self):
        return """
            CREATE TABLE user_projects (
                project_id varchar,
                user_id int4
            );
        """

    def user_labels_table(self):
        return """
            CREATE TABLE user_labels (
                user_id	int4,
                decibel_label_id int4
            );
        """

    def decibel_labels_table(self):
        return """
            CREATE TABLE decibel_labels (
                id	int4,
                rep_owner_key varchar,
                name varchar
            );
        """

    def phases_table(self):
        return """
            CREATE TABLE phases (
                id	int4,
                name varchar,
                project_id varchar,
                start_date date,
                end_date date
            );
        """

    def phase_campaigns_table(self):
        return """
            CREATE TABLE phase_campaigns (
                phase_id	int4,
                campaign_id varchar
            );
        """

    def campaign_platforms_table(self):
        return """
            CREATE TABLE campaign_platforms (
                campaign_id varchar,
                name varchar
            );
        """

    def campaign_sub_category_table(self):
        return """
            CREATE TABLE campaign_sub_category (
                id int2,
                name varchar
            );
        """

    def campaign_provider_table(self):
        return """
            CREATE TABLE campaign_provider (
                id bigint,
                name varchar
            );
        """

    def campaign_objective_table(self):
        return """
            CREATE TABLE campaign_objective (
                id int2,
                name varchar
            );
        """

    def campaign_category_table(self):
        return """
            CREATE TABLE campaign_category (
                id int2,
                name varchar
            );
        """

    def linkfire_campaign_table(self):
        return """
            CREATE TABLE IF NOT EXISTS linkfire_link_campaign (
                campaign_id VARCHAR NOT NULL  CONSTRAINT linkfire_link_campaign_campaign_id_fk REFERENCES campaigns (id),
                link_id    uuid NOT NULL,
                CONSTRAINT linkfire_campaign_pk PRIMARY KEY (campaign_id, link_id)
            );
        """

    def linkfire_project_table(self):
        return """
            CREATE TABLE IF NOT EXISTS linkfire_link_project (
                project_id VARCHAR NOT NULL CONSTRAINT linkfire_link_project_project_id_fk REFERENCES projects (id),
                link_id    uuid NOT NULL,
                CONSTRAINT linkfire_project_pk PRIMARY KEY (project_id, link_id)
            );
        """