-- ============================================================
--  GMV Max SaaS — Database Schema v2.0
-- ============================================================


-- Users
CREATE TABLE IF NOT EXISTS users (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    password        VARCHAR(255) NOT NULL,
    role            ENUM('admin','user') DEFAULT 'user',
    status          ENUM('active','suspended','pending') DEFAULT 'pending',
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login_at   DATETIME DEFAULT NULL
);

-- Subscriptions
CREATE TABLE IF NOT EXISTS subscriptions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    plan            ENUM('monthly','quarterly','yearly') DEFAULT 'monthly',
    status          ENUM('active','expired','cancelled') DEFAULT 'active',
    started_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    expired_at      DATETIME NOT NULL,
    notes           TEXT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- User Settings (TikTok credentials per user)
CREATE TABLE IF NOT EXISTS user_settings (
    id                      INT AUTO_INCREMENT PRIMARY KEY,
    user_id                 INT NOT NULL UNIQUE,
    tiktok_access_token     TEXT DEFAULT NULL,
    tiktok_advertiser_id    VARCHAR(50) DEFAULT NULL,
    tiktok_store_id         VARCHAR(50) DEFAULT NULL,
    tiktok_bc_id            VARCHAR(50) DEFAULT NULL,
    telegram_bot_token      VARCHAR(200) DEFAULT NULL,
    telegram_chat_id        VARCHAR(50) DEFAULT NULL,
    automation_enabled      TINYINT(1) DEFAULT 0,
    daily_report_enabled    TINYINT(1) DEFAULT 1,
    daily_report_hour       TINYINT DEFAULT 9,
    budget_min_guard        DECIMAL(15,2) DEFAULT 100000,
    budget_max_guard        DECIMAL(15,2) DEFAULT 10000000,
    check_interval          INT DEFAULT 1,
    updated_at              DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Rules
CREATE TABLE IF NOT EXISTS rules (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    user_id             INT NOT NULL,
    name                VARCHAR(150) NOT NULL,
    is_active           TINYINT(1) DEFAULT 1,
    condition_metric    VARCHAR(50) NOT NULL,
    condition_operator  VARCHAR(5) NOT NULL,
    condition_value     DECIMAL(15,4) NOT NULL,
    action_type         VARCHAR(50) NOT NULL,
    action_value        DECIMAL(15,4) DEFAULT NULL,
    min_spend           DECIMAL(15,2) DEFAULT 0,
    cooldown_hours      INT DEFAULT 24,
    apply_to            ENUM('all','specific') DEFAULT 'all',
    campaign_ids        TEXT DEFAULT NULL,
    schedule_type       ENUM('always','time_only','time_and_metric') DEFAULT 'always',
    schedule_hours      VARCHAR(100) DEFAULT NULL,
    schedule_days       VARCHAR(20) DEFAULT NULL,
    times_executed      INT DEFAULT 0,
    times_success       INT DEFAULT 0,
    last_executed_at    DATETIME DEFAULT NULL,
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Activity Logs
CREATE TABLE IF NOT EXISTS activity_logs (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    campaign_id     VARCHAR(50) DEFAULT NULL,
    campaign_name   VARCHAR(200) DEFAULT NULL,
    rule_id         INT DEFAULT NULL,
    rule_name       VARCHAR(150) DEFAULT NULL,
    action_type     VARCHAR(50) DEFAULT NULL,
    action_detail   TEXT DEFAULT NULL,
    metrics_roas    DECIMAL(10,4) DEFAULT NULL,
    metrics_spend   DECIMAL(15,2) DEFAULT NULL,
    metrics_gmv     DECIMAL(15,2) DEFAULT NULL,
    status          VARCHAR(20) DEFAULT NULL,
    error_msg       TEXT DEFAULT NULL,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Rule Executions (cooldown tracker per user)
CREATE TABLE IF NOT EXISTS rule_executions (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT NOT NULL,
    rule_id         INT NOT NULL,
    campaign_id     VARCHAR(50) NOT NULL,
    executed_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_rule_camp (user_id, rule_id, campaign_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- System Settings
CREATE TABLE IF NOT EXISTS system_settings (
    `key`   VARCHAR(100) PRIMARY KEY,
    `value` TEXT DEFAULT NULL
);

INSERT IGNORE INTO system_settings (`key`, `value`) VALUES
('app_name', 'GMV Max Autopilot'),
('app_version', '2.0.0');

-- Default admin (password: admin123 — GANTI SETELAH LOGIN!)
INSERT IGNORE INTO users (name, email, password, role, status)
VALUES ('Admin', 'admin@gmvmax.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uHd3tY8/i', 'admin', 'active');

-- User settings untuk admin
INSERT IGNORE INTO user_settings (user_id) 
SELECT id FROM users WHERE email='admin@gmvmax.com';
