CREATE DATABASE IF NOT EXISTS family_site CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE family_site;

CREATE TABLE admins (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_admins_username (username)
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(254) NOT NULL,
    contact VARCHAR(100) NULL,
    password_hash VARCHAR(255) NOT NULL,
    click_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    trader_id VARCHAR(100) NULL,
    qx_status ENUM('none','reg','conf','ftd') NOT NULL DEFAULT 'none',
    moderation_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    moderation_note VARCHAR(500) NULL,
    reviewed_by BIGINT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    total_deposit DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    deposit_currency CHAR(3) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    UNIQUE KEY uq_users_click_id (click_id),
    KEY idx_users_qx_status (qx_status),
    KEY idx_users_trader_id (trader_id),
    KEY idx_users_moderation_status (moderation_status),
    CONSTRAINT fk_users_reviewed_by FOREIGN KEY (reviewed_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE clicks (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    click_id CHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    ip VARBINARY(16) NULL,
    user_agent VARCHAR(512) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_clicks_user_created (user_id, created_at),
    CONSTRAINT fk_clicks_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE postback_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    event_id VARCHAR(150) NULL,
    click_id VARCHAR(64) NULL,
    status VARCHAR(40) NULL,
    trader_id VARCHAR(100) NULL,
    amount DECIMAL(14,2) NULL,
    currency CHAR(3) NULL,
    raw_payload LONGTEXT NOT NULL,
    ip VARBINARY(16) NULL,
    result ENUM('received','matched','no_match','duplicate','rejected','invalid','processing_error') NOT NULL DEFAULT 'received',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_postback_event_id (event_id),
    KEY idx_postback_click_created (click_id, created_at),
    KEY idx_postback_result_created (result, created_at)
) ENGINE=InnoDB;

CREATE TABLE processed_postback_events (
    event_id VARCHAR(150) NOT NULL PRIMARY KEY,
    click_id VARCHAR(64) NOT NULL,
    status VARCHAR(40) NOT NULL,
    processed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE admin_actions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    admin_id BIGINT UNSIGNED NOT NULL,
    action VARCHAR(100) NOT NULL,
    details JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_admin_actions_created (created_at),
    CONSTRAINT fk_admin_actions_admin FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE settings (
    setting_key VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value TEXT NOT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE login_attempts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ip VARBINARY(16) NULL,
    identifier_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_login_attempt_window (identifier_hash, attempted_at),
    KEY idx_login_ip_window (ip, attempted_at)
) ENGINE=InnoDB;