CREATE OR REPLACE FUNCTION update_updated_on_column()
RETURNS TRIGGER AS $$
BEGIN
   IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN
      NEW.updated_on = NOW() AT TIME ZONE 'UTC';
      RETURN NEW;
   ELSE
      RETURN OLD;
   END IF;
END;
$$ language 'plpgsql';

CREATE TYPE domain_level AS ENUM (
  'SYSTEM',
  'USER'
);

CREATE TYPE domain_service AS ENUM (
  'LINKS',
  'SHORT',
  'LIST'
);

CREATE TYPE domain_status AS ENUM (
  'PENDING',
  'APPROVED',
  'ERROR'
);

CREATE TYPE invoice_status AS ENUM (
  'PENDING',
  'PAID',
  'REFUNDED',
  'PARTIAL_REFUND'
);

CREATE TYPE org_link_visibility AS ENUM (
  'PUBLIC',
  'PRIVATE',
  'RESTRICTED'
);

CREATE TYPE org_link_type AS ENUM (
  'LINK',
  'NOTE'
);

CREATE TYPE org_link_perm AS ENUM (
  'READ',
  'WRITE',
  'ADMIN_WRITE'
);

CREATE TYPE org_type AS ENUM (
  'USER',
  'NORMAL'
);

CREATE TYPE subscription_type AS ENUM (
  'PERSONAL',
  'BUSINESS'
);

CREATE TYPE qr_code_type AS ENUM (
  'LIST',
  'SHORT'
);

CREATE TYPE totals_meta_type AS ENUM (
  'REFERRER',
  'COUNTRY',
  'CITY',
  'DEVICE'
);


CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  full_name VARCHAR ( 150 ) NOT NULL,
  password VARCHAR ( 1024 ) NOT NULL,
  email VARCHAR ( 255 ) UNIQUE NOT NULL,
  picture VARCHAR(1024) DEFAULT '',
  settings JSONB DEFAULT '{}',
  is_verified BOOLEAN DEFAULT FALSE,
  is_superuser BOOLEAN DEFAULT FALSE,
  is_staff BOOLEAN DEFAULT FALSE,
  is_locked BOOLEAN DEFAULT FALSE,
  lock_reason VARCHAR (255) DEFAULT '',
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login TIMESTAMPTZ DEFAULT NULL
);

CREATE INDEX users_id_idx ON users (id);

CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  owner_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  org_type org_type DEFAULT 'USER',
  name VARCHAR ( 150 ) NOT NULL,
  slug VARCHAR ( 150 ) UNIQUE NOT NULL,
  image VARCHAR(1024) DEFAULT '',
  settings JSONB DEFAULT '{}',
  timezone VARCHAR(20) NOT NULL DEFAULT 'UTC',
  is_active BOOLEAN DEFAULT TRUE,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX organizations_id_idx ON organizations (id);
CREATE INDEX organizations_owner_id_idx ON organizations (owner_id);
CREATE INDEX organizations_slug_idx ON organizations (slug);
CREATE TRIGGER update_organizations_modtime BEFORE UPDATE ON organizations FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE sessions (
  token TEXT PRIMARY KEY,
  data BYTEA NOT NULL,
  expiry TIMESTAMPTZ NOT NULL
);

CREATE INDEX sessions_expiry_idx ON sessions (expiry);


CREATE TABLE confirmations (
  id SERIAL PRIMARY KEY,
  "type" INT NOT NULL, -- email, password reset
  confirmation_target VARCHAR DEFAULT NULL,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  key VARCHAR NOT NULL UNIQUE,
  confirm_time TIMESTAMPTZ DEFAULT NULL,
  expire_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP + interval '24 hours',
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE email_notifications (
  id SERIAL PRIMARY KEY,
  email VARCHAR ( 255 ) UNIQUE NOT NULL,
  count INT NOT NULL DEFAULT 0,
  notifications JSON NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX email_notifications_id_idx on email_notifications (id);
CREATE TRIGGER update_email_notifications_modtime BEFORE UPDATE ON email_notifications FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE base_urls (
  id SERIAL PRIMARY KEY,
  title VARCHAR ( 150 ) DEFAULT '',
  url TEXT UNIQUE NOT NULL,
  public_ready BOOLEAN DEFAULT FALSE,
  hash VARCHAR(128) UNIQUE NOT NULL,
  data JSONB DEFAULT '{}',
  counter INT DEFAULT 0,
  parse_attempts INT DEFAULT 0,
  last_parse_attempt TIMESTAMPTZ,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX base_urls_id_idx ON base_urls (id);
CREATE TRIGGER base_urls_modtime BEFORE UPDATE ON base_urls FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE org_links (
  id SERIAL PRIMARY KEY,
  title VARCHAR ( 150 ) NOT NULL,
  url TEXT NOT NULL,
  description TEXT DEFAULT '',
  "type" org_link_type default 'LINK',
  hash VARCHAR(128) UNIQUE NOT NULL,
  base_url_id INT REFERENCES base_urls (id) ON DELETE CASCADE,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE SET NULL,
  visibility org_link_visibility DEFAULT 'PUBLIC',
  unread BOOLEAN DEFAULT FALSE NOT NULL,
  starred BOOLEAN DEFAULT FALSE NOT NULL,
  archive_url TEXT DEFAULT '',
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
  CONSTRAINT unique_base_url_org UNIQUE (base_url_id, org_id)
);

CREATE INDEX org_links_id_idx ON org_links (id);
CREATE INDEX org_links_base_url_id_idx ON org_links (base_url_id);
CREATE INDEX org_links_org_id_idx ON org_links (org_id);
CREATE INDEX org_links_user_id_idx ON org_links (user_id);
CREATE INDEX org_links_search_idx ON org_links USING GIN (to_tsvector('simple', title || ' ' || description || ' ' || url));
CREATE TRIGGER update_org_links_modtime BEFORE UPDATE ON org_links FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE organization_users (
  id SERIAL PRIMARY KEY,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  permission org_link_perm default 'READ',
  is_active BOOLEAN DEFAULT TRUE,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_user_org UNIQUE (org_id, user_id)
);

CREATE INDEX organization_users_id_idx ON organization_users (id);
CREATE INDEX organization_users_org_id_idx ON organization_users (org_id);
CREATE INDEX organization_users_user_id_idx ON organization_users (user_id);
CREATE TRIGGER update_organization_users_modtime BEFORE UPDATE ON organization_users FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();


CREATE TABLE domains (
  id SERIAL PRIMARY KEY,
  name VARCHAR (255) NOT NULL,
  lookup_name VARCHAR (500) NOT NULL, -- padded in case...
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE,
  level domain_level NOT NULL,
  service domain_service NOT NULL,
  status domain_status DEFAULT 'PENDING' NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  last_action TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_lookup_name_active UNIQUE (lookup_name, is_active)
);

CREATE INDEX domains_id_idx ON domains (id);
CREATE INDEX domains_org_id_idx ON domains (org_id);
CREATE TRIGGER update_domains_modtime BEFORE UPDATE ON domains FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();


CREATE TABLE oauth2_clients (
        id serial PRIMARY KEY,
        owner_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE NOT NULL,
        name character varying(256) NOT NULL,
        description character varying,
        key character varying NOT NULL UNIQUE,
        secret_hash character varying(128) NOT NULL,
        secret_partial character varying(8) NOT NULL,
        redirect_url character varying,
        client_url character varying,
        revoked boolean DEFAULT false NOT NULL,
        created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX oauth2_clients_id_idx ON oauth2_clients (id);
CREATE INDEX oauth2_clients_owner_id_idx ON oauth2_clients (owner_id);
CREATE TRIGGER update_oauth2_clients_modtime BEFORE UPDATE ON oauth2_clients FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();


CREATE TABLE oauth2_grants (
        id serial PRIMARY KEY,
        issued TIMESTAMPTZ NOT NULL,
        expires TIMESTAMPTZ NOT NULL,
        comment character varying,
        token_hash character varying(128) NOT NULL,
        refresh_token_hash character varying(128) NOT NULL,
        grants character varying default '',
        user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE,
        client_id integer REFERENCES oauth2_clients (id) ON DELETE CASCADE
);

CREATE INDEX oauth2_grants_id_idx ON oauth2_grants (id);
CREATE INDEX oauth2_grants_user_id_idx ON oauth2_grants (user_id);
CREATE INDEX oauth2_grants_client_id_idx ON oauth2_grants (client_id);

CREATE TABLE oauth2_authorizations (
        id serial PRIMARY KEY,
        code character varying(128) NOT NULL,
        payload character varying NOT NULL,
        created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX oauth2_authorizations_id_idx ON oauth2_authorizations (id);
CREATE INDEX oauth2_authorizations_code_idx ON oauth2_authorizations (code);

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR ( 50 ) NOT NULL,
  slug VARCHAR ( 50 ) UNIQUE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX tags_id_idx ON tags (id);
CREATE INDEX tags_id_slug ON tags (slug);

CREATE TABLE tag_links (
  id SERIAL PRIMARY KEY,
  org_link_id INT REFERENCES org_links (id) ON DELETE CASCADE NOT NULL,
  tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_tag_link UNIQUE (org_link_id, tag_id)
);

CREATE INDEX tag_links_id_idx ON tag_links (id);
CREATE INDEX tag_links_org_link_id_idx ON tag_links (org_link_id);
CREATE INDEX tag_links_tag_id_idx ON tag_links (tag_id);

CREATE TABLE slack_connections (
  id SERIAL PRIMARY KEY,
  token TEXT NOT NULL,
  team_id TEXT UNIQUE NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL UNIQUE,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX slack_connections_id_idx ON slack_connections (id);
CREATE INDEX slack_connections_org_id_idx ON slack_connections (org_id);

CREATE TABLE slack_users (
  id SERIAL PRIMARY KEY,
  slack_conn_id INT REFERENCES slack_connections (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  slack_user TEXT NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_slack_conn_user UNIQUE (slack_conn_id, user_id)
);

CREATE INDEX slack_users_id_idx ON slack_users (id);
CREATE INDEX slack_users_slack_conn_id_idx ON slack_users (slack_conn_id);

CREATE TABLE mattermost_connections (
  id SERIAL PRIMARY KEY,
  team_id TEXT UNIQUE NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL UNIQUE,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX mattermost_connections_id_idx ON mattermost_connections (id);
CREATE INDEX mattermost_connections_org_id_idx ON mattermost_connections (org_id);

CREATE TABLE mattermost_users (
  id SERIAL PRIMARY KEY,
  mattermost_conn_id INT REFERENCES mattermost_connections (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  mattermost_user TEXT NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_mattermost_conn_user UNIQUE (mattermost_conn_id, user_id)
);

CREATE INDEX mattermost_users_id_idx ON mattermost_users (id);
CREATE INDEX mattermost_users_mattermost_conn_id_idx ON mattermost_users (mattermost_conn_id);

CREATE TABLE link_shorts (
  id SERIAL PRIMARY KEY,
  title VARCHAR ( 150 ) DEFAULT '',
  url TEXT NOT NULL,
  short_code TEXT DEFAULT '',
  domain_id INT REFERENCES domains (id) ON DELETE RESTRICT NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE SET NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_short_code_domain UNIQUE (short_code, domain_id)
);

CREATE INDEX link_shorts_id_idx ON link_shorts (id);
CREATE INDEX link_shorts_domain_id_idx ON link_shorts (domain_id);
CREATE INDEX link_shorts_org_id_idx ON link_shorts (org_id);
CREATE INDEX link_shorts_user_id_idx ON link_shorts (user_id);
CREATE TRIGGER update_link_shorts_modtime BEFORE UPDATE ON link_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE tag_link_shorts (
  id SERIAL PRIMARY KEY,
  link_short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE NOT NULL,
  tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_tag_link_short UNIQUE (link_short_id, tag_id)
);

CREATE INDEX tag_link_shorts_id_idx ON tag_link_shorts (id);
CREATE INDEX tag_link_shorts_link_short_id_idx ON tag_link_shorts (link_short_id);
CREATE INDEX tag_link_shorts_tag_id_idx ON tag_link_shorts (tag_id);

CREATE TABLE listings (
  id SERIAL PRIMARY KEY,
  title VARCHAR ( 150 ) NOT NULL,
  slug VARCHAR ( 150 ) NOT NULL,
  image VARCHAR(1024) DEFAULT '',
  metadata JSONB DEFAULT '{}',
  domain_id INT REFERENCES domains (id) ON DELETE RESTRICT NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  user_id INT REFERENCES users (id) ON DELETE SET NULL,
  is_default BOOLEAN DEFAULT FALSE,
  is_active BOOLEAN DEFAULT TRUE,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_slug_domain UNIQUE (slug, domain_id)
);

CREATE INDEX listings_id_idx ON listings (id);
CREATE INDEX listings_domain_id_idx ON listings (domain_id);
CREATE INDEX listings_org_id_idx ON listings (org_id);
CREATE INDEX listings_domain_user_id_idx ON listings (user_id);
CREATE TRIGGER update_listings_modtime BEFORE UPDATE ON link_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE tag_listings (
  id SERIAL PRIMARY KEY,
  listing_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL,
  tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_tag_listing UNIQUE (listing_id, tag_id)
);

CREATE INDEX tag_listings_id_idx ON tag_listings (id);
CREATE INDEX tag_listings_listing_id_idx ON tag_listings (listing_id);
CREATE INDEX tag_listings_tag_id_idx ON tag_listings (tag_id);

CREATE TABLE listing_links (
  id SERIAL PRIMARY KEY,
  title VARCHAR ( 150 ) NOT NULL,
  listing_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL,
  link_order INT DEFAULT 0,
  url TEXT NOT NULL,
  description TEXT DEFAULT '',
  user_id INT REFERENCES users (id) ON DELETE SET NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX listing_links_id_idx ON listing_links (id);
CREATE INDEX listing_links_listing_id_idx ON listing_links (listing_id);
CREATE INDEX listing_links_user_id_idx ON listing_links (user_id);
CREATE TRIGGER update_listing_links_modtime BEFORE UPDATE ON listing_links FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE qr_codes (
  id SERIAL PRIMARY KEY,
  title VARCHAR (150) NOT NULL,
  hash_id VARCHAR(10) UNIQUE NOT NULL,
  url TEXT NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  code_type qr_code_type NOT NULL,
  image_path TEXT DEFAULT '',
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX qr_codes_id_idx ON qr_codes (id);
CREATE INDEX qr_codes_hash_id_idx ON qr_codes (hash_id);
CREATE INDEX qr_codes_org_id_idx ON qr_codes (org_id);
CREATE INDEX qr_codes_user_id_idx ON qr_codes (user_id);
CREATE TRIGGER update_qr_codes_modtime BEFORE UPDATE ON qr_codes FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE qr_codes_shorts (
  id SERIAL PRIMARY KEY,
  qr_code_id INT REFERENCES qr_codes (id) ON DELETE CASCADE NOT NULL,
  short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX qr_codes_shorts_id_idx ON qr_codes_shorts (id);
CREATE INDEX qr_codes_shorts_code_id_idx ON qr_codes_shorts (qr_code_id);
CREATE INDEX qr_codes_shorts_short_id_idx ON qr_codes_shorts (short_id);
CREATE TRIGGER update_qr_codes_shorts_modtime BEFORE UPDATE ON qr_codes_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE qr_codes_lists (
  id SERIAL PRIMARY KEY,
  qr_code_id INT REFERENCES qr_codes (id) ON DELETE CASCADE NOT NULL,
  list_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX qr_codes_lists_id_idx ON qr_codes_lists (id);
CREATE INDEX qr_codes_lists_code_id_idx ON qr_codes_lists (qr_code_id);
CREATE INDEX qr_codes_lists_list_id_idx ON qr_codes_lists (list_id);

CREATE TABLE daily_totals (
    id SERIAL PRIMARY KEY,
    clicks INT DEFAULT 0,
    unique_clicks INT DEFAULT 0,
    "date" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    link_short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE DEFAULT NULL,
    list_id INT REFERENCES listings (id) ON DELETE CASCADE DEFAULT NULL,
    listing_link_id INT REFERENCES listing_links (id) ON DELETE CASCADE DEFAULT NULL,
    qr_id INT REFERENCES qr_codes (id) ON DELETE CASCADE DEFAULT NULL,

    -- Check constraint to ensure only one of the three columns is NOT NULL
    CONSTRAINT only_one_not_null_constraint
    CHECK (
        (listing_link_id IS NOT NULL AND link_short_id IS NULL AND list_id IS NULL AND qr_id IS NULL) OR
        (listing_link_id IS NULL AND link_short_id IS NOT NULL AND list_id IS NULL AND qr_id IS NULL) OR
        (listing_link_id IS NULL AND link_short_id IS NULL AND list_id IS NOT NULL AND qr_id IS NULL) OR
        (listing_link_id IS NULL AND link_short_id IS NULL AND list_id IS NULL AND qr_id IS NOT NULL)
    )
);

CREATE INDEX daily_totals_id_idx ON daily_totals (id);
CREATE INDEX daily_totals_link_short_id_idx ON daily_totals (link_short_id);
CREATE INDEX daily_totals_list_id_idx ON daily_totals (list_id);
CREATE INDEX daily_totals_listing_link_id_idx ON daily_totals (listing_link_id);
CREATE INDEX daily_totals_qr_id_idx ON daily_totals (qr_id);

CREATE TABLE totals_meta (
    id SERIAL PRIMARY KEY,
    daily_total_id INT REFERENCES daily_totals (id) ON DELETE CASCADE NOT NULL,
    meta_type totals_meta_type DEFAULT 'REFERRER',
    meta_value VARCHAR(1024) DEFAULT '',
    clicks INT DEFAULT 0,
    unique_clicks INT DEFAULT 0,
    CONSTRAINT unique_daily_total_meta UNIQUE (daily_total_id, meta_type, meta_value)
);

CREATE INDEX totals_meta_id_idx ON totals_meta (id);
CREATE INDEX totals_meta_daily_total_id_idx ON totals_meta (daily_total_id);

CREATE TABLE subscription_plans (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  plan_id VARCHAR(150) NOT NULL,
  stripe_price_id VARCHAR(150) NOT NULL,
  price INT NOT NULL CHECK (price > 0),
  "type" subscription_type NOT NULL DEFAULT 'PERSONAL',
  is_active BOOLEAN DEFAULT true,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX subscription_plans_id_idx ON subscription_plans (id);
CREATE TRIGGER update_subscription_plans_modtime BEFORE UPDATE ON subscription_plans FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE subscriptions (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  stripe_id VARCHAR(150) UNIQUE NOT NULL,
  subscription_plan_id INT REFERENCES subscription_plans (id) ON DELETE RESTRICT DEFAULT NULL,
  start_date TIMESTAMPTZ DEFAULT NULL,
  end_date TIMESTAMPTZ DEFAULT NULL,
  cancel_end BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX subscriptions_id_idx ON subscriptions (id);
CREATE INDEX subscriptions_user_id_idx ON subscriptions (user_id);
CREATE INDEX subscriptions_org_id_idx ON subscriptions (org_id);
CREATE TRIGGER update_subscriptions_modtime BEFORE UPDATE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  status invoice_status DEFAULT 'PENDING',
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  stripe_id VARCHAR(150),
  subscription_id INT REFERENCES subscriptions (id) ON DELETE RESTRICT DEFAULT NULL,
  currency VARCHAR(10) DEFAULT 'USD',
  amount INT NOT NULL,
  amount_paid INT DEFAULT 0,
  amount_net INT DEFAULT 0,
  amount_refunded INT DEFAULT 0,
  payment_fee INT DEFAULT 0,
  hosted_invoice_url VARCHAR(1024),
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX invoices_id_idx ON invoices (id);
CREATE INDEX invoices_user_id_idx ON invoices (user_id);
CREATE INDEX invoices_org_id_idx ON invoices (org_id);
CREATE TRIGGER update_invoices_modtime BEFORE UPDATE ON invoices FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column();

CREATE TABLE followers (
  id SERIAL PRIMARY KEY,
  user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL,
  org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL,
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT unique_followers UNIQUE (user_id, org_id)
);

CREATE INDEX followers_id_idx ON followers (id);
CREATE INDEX followers_user_id_idx ON followers (user_id);
CREATE INDEX followers_org_id_idx ON followers (org_id);

CREATE TABLE audit_log (
  id serial PRIMARY KEY,
  user_id INTEGER REFERENCES users (id) ON DELETE CASCADE,
  ip_address character varying(50) NOT NULL,
  event_type character varying(256) NOT NULL,
  details character varying(512),
  metadata JSONB DEFAULT '{}',
  created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX audit_log_id_idx ON audit_log (id);
CREATE INDEX audit_log_created_on_idx ON audit_log (created_on);