-- Fraud Warden Relay - Database Schema
-- Run this once in phpMyAdmin (or via `mysql < schema.sql`) to create the
-- database structure. Uses InnoDB for proper transaction/locking support
-- under concurrent requests from many customers at once.

CREATE TABLE customers (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email           VARCHAR(255) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    api_key         VARCHAR(64) NOT NULL UNIQUE,
    paddle_customer_id VARCHAR(64) NULL UNIQUE,
    status          ENUM('active', 'suspended') NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per completed Paddle transaction, so a later refund
-- (adjustment.created) can be traced back to the customer it belongs to.
CREATE TABLE transactions (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id             INT UNSIGNED NOT NULL,
    paddle_transaction_id   VARCHAR(64) NOT NULL UNIQUE,
    paddle_customer_id      VARCHAR(64) NOT NULL,
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One-time links emailed to a customer right after purchase (they never
-- set a password during Paddle Checkout) so they can set one and reach
-- their dashboard.
CREATE TABLE password_setup_tokens (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id     INT UNSIGNED NOT NULL,
    token           VARCHAR(64) NOT NULL UNIQUE,
    expires_at      DATETIME NOT NULL,
    used_at         DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per Google Ads connection a customer has authorized. A customer
-- could in principle connect more than one Google account, so this is a
-- separate table rather than columns on `customers`.
CREATE TABLE google_connections (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id         INT UNSIGNED NOT NULL,
    google_email        VARCHAR(255) NOT NULL,
    -- Encrypted at rest with openssl_encrypt() before insert - never store
    -- the raw refresh token in the database.
    refresh_token_enc   TEXT NOT NULL,
    connected_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which Google Ads customer IDs (accounts) this connection is allowed to
-- act on. Populated automatically after OAuth by calling Google's
-- ListAccessibleCustomers, so the customer never has to type account IDs
-- by hand.
CREATE TABLE google_ads_accounts (
    id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    google_connection_id    INT UNSIGNED NOT NULL,
    google_ads_customer_id  VARCHAR(20) NOT NULL,
    account_name            VARCHAR(255) NULL,
    is_mcc                  TINYINT(1) NOT NULL DEFAULT 0,
    FOREIGN KEY (google_connection_id) REFERENCES google_connections(id) ON DELETE CASCADE,
    UNIQUE KEY unique_account (google_connection_id, google_ads_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every block attempt, successful or not. This is what makes the "800
-- clicks worth of budget saved" number on the customer's dashboard
-- possible later, and is essential for debugging support tickets.
CREATE TABLE block_log (
    id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id             INT UNSIGNED NOT NULL,
    google_ads_customer_id  VARCHAR(20) NOT NULL,
    campaign_id             VARCHAR(20) NOT NULL,
    ip_or_cidr              VARCHAR(64) NOT NULL,
    status                  ENUM('success', 'failed') NOT NULL,
    error_message           VARCHAR(500) NULL,
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_customer_date (customer_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;