jam-cloud/db/up/affiliate_partners2.sql

CREATE TABLE affiliate_legalese (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    legalese TEXT,
    version INTEGER NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users DROP CONSTRAINT users_affiliate_referral_id_fkey;
ALTER TABLE users DROP COLUMN affiliate_referral_id;
DROP TABLE affiliate_partners;

CREATE TABLE affiliate_partners (
  id INTEGER PRIMARY KEY,
  partner_name VARCHAR(1000),
  partner_user_id VARCHAR(64) REFERENCES users(id) ON DELETE SET NULL,
  entity_type VARCHAR(64),
  legalese_id VARCHAR(64),
  signed_at TIMESTAMP,
  last_paid_at TIMESTAMP,
  address JSON NOT NULL DEFAULT '{}',
  tax_identifier VARCHAR(1000),
  referral_user_count INTEGER NOT NULL DEFAULT 0,
  cumulative_earnings_in_cents INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE SEQUENCE partner_key_sequence;
ALTER SEQUENCE partner_key_sequence RESTART WITH 10000;
ALTER TABLE affiliate_partners ALTER COLUMN id SET DEFAULT nextval('partner_key_sequence');;

ALTER TABLE users ADD COLUMN affiliate_referral_id INTEGER REFERENCES affiliate_partners(id) ON DELETE SET NULL;
CREATE INDEX affiliate_partners_legalese_idx ON affiliate_partners(legalese_id);

CREATE UNLOGGED TABLE affiliate_referral_visits (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  affiliate_partner_id INTEGER NOT NULL,
  ip_address VARCHAR NOT NULL,
  visited_url VARCHAR,
  referral_url VARCHAR,
  first_visit BOOLEAN NOT NULL DEFAULT TRUE,
  user_id VARCHAR(64),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX on affiliate_referral_visits (affiliate_partner_id, created_at);

CREATE TABLE affiliate_quarterly_payments (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  quarter INTEGER NOT NULL,
  year INTEGER NOT NULL,
  affiliate_partner_id INTEGER NOT NULL REFERENCES affiliate_partners(id),
  due_amount_in_cents INTEGER NOT NULL DEFAULT 0,
  paid BOOLEAN NOT NULL DEFAULT FALSE,
  closed BOOLEAN NOT NULL DEFAULT FALSE,
  jamtracks_sold INTEGER NOT NULL DEFAULT 0,
  closed_at TIMESTAMP,
  paid_at TIMESTAMP,
  last_updated TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE affiliate_monthly_payments (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
  month INTEGER NOT NULL,
  year INTEGER NOT NULL,
  affiliate_partner_id INTEGER NOT NULL REFERENCES affiliate_partners(id),
  due_amount_in_cents INTEGER NOT NULL DEFAULT 0,
  closed BOOLEAN NOT NULL DEFAULT FALSE,
  jamtracks_sold INTEGER NOT NULL DEFAULT 0,
  closed_at TIMESTAMP,
  last_updated TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE INDEX ON affiliate_quarterly_payments (affiliate_partner_id, year, quarter);
CREATE UNIQUE INDEX ON affiliate_quarterly_payments (year, quarter, affiliate_partner_id);
CREATE UNIQUE INDEX ON affiliate_monthly_payments (year, month, affiliate_partner_id);
CREATE INDEX ON affiliate_monthly_payments (affiliate_partner_id, year, month);


ALTER TABLE sale_line_items ADD COLUMN affiliate_referral_id INTEGER REFERENCES affiliate_partners(id);
ALTER TABLE sale_line_items ADD COLUMN affiliate_referral_fee_in_cents INTEGER;
ALTER TABLE sale_line_items ADD COLUMN affiliate_refunded BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE sale_line_items ADD COLUMN affiliate_refunded_at TIMESTAMP;
ALTER TABLE generic_state ADD COLUMN affiliate_tallied_at TIMESTAMP;

CREATE TABLE affiliate_traffic_totals (
  day     DATE NOT NULL,
  signups INTEGER NOT NULL DEFAULT 0,
  visits  INTEGER NOT NULL DEFAULT 0,
  affiliate_partner_id INTEGER NOT NULL REFERENCES affiliate_partners(id),
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX ON affiliate_traffic_totals (day, affiliate_partner_id);
CREATE INDEX ON affiliate_traffic_totals (affiliate_partner_id, day);

CREATE VIEW affiliate_payments AS
    SELECT  id AS monthly_id,
            CAST(NULL as VARCHAR) AS quarterly_id,
            affiliate_partner_id,
            due_amount_in_cents,
            jamtracks_sold,
            created_at,
            closed,
            CAST(NULL AS BOOLEAN) AS paid,
            year,
            month as month,
            CAST(NULL AS INTEGER) as quarter,
            month as time_sort,
            'monthly' AS payment_type
    FROM affiliate_monthly_payments
    UNION ALL
    SELECT  CAST(NULL as VARCHAR) AS monthly_id,
            id AS quarterly_id,
            affiliate_partner_id,
            due_amount_in_cents,
            jamtracks_sold,
            created_at,
            closed,
            paid,
            year,
            CAST(NULL AS INTEGER) as month,
            quarter,
            (quarter * 3) + 3 as time_sort,
            'quarterly' AS payment_type
    FROM affiliate_quarterly_payments;