-- ============================================================
-- Migration 005: Auth tables + user_id di tabel existing
-- ============================================================

-- 1. Tabel admins
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(200) NOT NULL UNIQUE,
    password_hash VARCHAR(256) NOT NULL,
    is_superadmin BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Tabel users
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(80) NOT NULL UNIQUE,
    email VARCHAR(200) NOT NULL UNIQUE,
    password_hash VARCHAR(256) NOT NULL,
    display_name VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    is_approved BOOLEAN DEFAULT FALSE,
    approved_by INT,
    approved_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME,
    CONSTRAINT fk_users_approved_by FOREIGN KEY (approved_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. Tabel user_settings
CREATE TABLE IF NOT EXISTS user_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value JSON NOT NULL,
    category ENUM('capital', 'fees', 'display') NOT NULL DEFAULT 'display',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_settings_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uq_user_settings_key UNIQUE (user_id, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Tambah user_id ke portfolio (NULLABLE dulu)
ALTER TABLE portfolio ADD COLUMN user_id INT AFTER id;
ALTER TABLE portfolio ADD CONSTRAINT fk_portfolio_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 5. Tambah user_id ke trade_history
ALTER TABLE trade_history ADD COLUMN user_id INT AFTER id;
ALTER TABLE trade_history ADD CONSTRAINT fk_trade_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 6. Tambah user_id ke watchlist
ALTER TABLE watchlist ADD COLUMN user_id INT AFTER id;
ALTER TABLE watchlist ADD CONSTRAINT fk_watchlist_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 7. Tambah user_id + sharing columns ke watchlist_groups
ALTER TABLE watchlist_groups ADD COLUMN user_id INT AFTER id;
ALTER TABLE watchlist_groups ADD COLUMN is_shared BOOLEAN DEFAULT FALSE;
ALTER TABLE watchlist_groups ADD COLUMN share_token VARCHAR(36);
ALTER TABLE watchlist_groups ADD CONSTRAINT fk_watchlist_groups_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- 8. Tambah user_id ke capital_allocations
ALTER TABLE capital_allocations ADD COLUMN user_id INT AFTER id;
ALTER TABLE capital_allocations ADD CONSTRAINT fk_capital_allocations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;