-- B-connex initial schema (MySQL 8 / utf8mb4)

CREATE TABLE IF NOT EXISTS organizations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    abn VARCHAR(20) NULL,
    country CHAR(2) NOT NULL DEFAULT 'AU',
    currency CHAR(3) NOT NULL DEFAULT 'AUD',
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(120) NOT NULL,
    role ENUM('owner','maintainer','subscriber','operator') NOT NULL DEFAULT 'subscriber',
    status ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_users_org (org_id),
    CONSTRAINT fk_users_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    slug VARCHAR(40) NOT NULL UNIQUE,
    name VARCHAR(80) NOT NULL,
    tier VARCHAR(40) NOT NULL,
    price_aud_cents INT UNSIGNED NOT NULL DEFAULT 0,
    integrations_max INT UNSIGNED NOT NULL DEFAULT 1,
    seats INT UNSIGNED NOT NULL DEFAULT 1,
    ai_provider VARCHAR(40) NOT NULL DEFAULT 'local',
    features JSON NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    plan_id BIGINT UNSIGNED NOT NULL,
    status ENUM('active','past_due','cancelled','trialing') NOT NULL DEFAULT 'active',
    provider VARCHAR(40) NOT NULL DEFAULT 'manual',
    provider_ref VARCHAR(120) NULL,
    starts_at DATETIME NOT NULL,
    renews_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_sub_org (org_id),
    CONSTRAINT fk_sub_org  FOREIGN KEY (org_id)  REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS licenses (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,
    license_key VARCHAR(64) NOT NULL UNIQUE,
    tier VARCHAR(40) NOT NULL,
    seats INT UNSIGNED NOT NULL DEFAULT 1,
    status ENUM('active','revoked','expired') NOT NULL DEFAULT 'active',
    expires_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_lic_org (org_id),
    CONSTRAINT fk_lic_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_lic_sub FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integrations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(40) NOT NULL,
    label VARCHAR(120) NOT NULL,
    access_token_enc TEXT NULL,
    refresh_token_enc TEXT NULL,
    expires_at DATETIME NULL,
    scopes VARCHAR(255) NULL,
    status ENUM('connected','disconnected','error') NOT NULL DEFAULT 'connected',
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_int_org (org_id),
    CONSTRAINT fk_int_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    channel VARCHAR(40) NOT NULL,
    status ENUM('draft','active','paused','completed') NOT NULL DEFAULT 'draft',
    config_json JSON NULL,
    stats_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_camp_org (org_id),
    CONSTRAINT fk_camp_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leads (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    campaign_id BIGINT UNSIGNED NULL,
    full_name VARCHAR(150) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(40) NULL,
    company VARCHAR(150) NULL,
    title VARCHAR(120) NULL,
    source VARCHAR(60) NULL,
    enrichment_json JSON NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'new',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_lead_org (org_id),
    KEY idx_lead_camp (campaign_id),
    CONSTRAINT fk_lead_org  FOREIGN KEY (org_id)      REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_lead_camp FOREIGN KEY (campaign_id) REFERENCES campaigns(id)     ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NULL,
    invoice_number VARCHAR(40) NOT NULL UNIQUE,
    currency CHAR(3) NOT NULL DEFAULT 'AUD',
    subtotal_cents INT UNSIGNED NOT NULL DEFAULT 0,
    gst_cents INT UNSIGNED NOT NULL DEFAULT 0,
    total_cents INT UNSIGNED NOT NULL DEFAULT 0,
    description VARCHAR(255) NULL,
    abn VARCHAR(20) NULL,
    status ENUM('draft','issued','paid','void') NOT NULL DEFAULT 'issued',
    issued_at DATETIME NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_inv_org (org_id),
    CONSTRAINT fk_inv_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    meta_json JSON NULL,
    ip VARCHAR(45) NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_audit_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    name VARCHAR(80) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    last_used_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_tok_org (org_id),
    CONSTRAINT fk_tok_org FOREIGN KEY (org_id) REFERENCES organizations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
