-- ===============================================
-- SaniBook Live Deployment SQL
-- Date: 2026-02-14
-- Run each statement one at a time in phpMyAdmin
-- Skip any that give "Duplicate column" errors
-- ===============================================

-- 1. Token Pricing Settings
INSERT INTO settings (setting_key, setting_value) VALUES 
    ('token_cost_per_char', '1'),
    ('token_cost_per_image', '2500'),
    ('token_cost_per_video_sec', '100'),
    ('token_cost_per_audio_sec', '10')
ON DUPLICATE KEY UPDATE 
    setting_key = VALUES(setting_key);

-- 2. Audit Log Table
CREATE TABLE IF NOT EXISTS audit_log (
    id INT NOT NULL AUTO_INCREMENT,
    action_type VARCHAR(50) NOT NULL,
    target_table VARCHAR(50) DEFAULT NULL,
    target_id INT DEFAULT NULL,
    user_id INT DEFAULT NULL,
    admin_id INT DEFAULT NULL,
    old_values JSON DEFAULT NULL,
    new_values JSON DEFAULT NULL,
    note TEXT DEFAULT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. User profile columns (run one at a time, skip duplicates)
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT NULL;
ALTER TABLE users ADD COLUMN bio TEXT DEFAULT NULL;
ALTER TABLE users ADD COLUMN categories JSON DEFAULT NULL;
ALTER TABLE users ADD COLUMN password_reset_token VARCHAR(64) DEFAULT NULL;
ALTER TABLE users ADD COLUMN password_reset_expires DATETIME DEFAULT NULL;

-- 4. SEO-friendly profile slug (run this one separately)
ALTER TABLE users ADD COLUMN slug VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD UNIQUE INDEX idx_slug (slug);

-- 5. Google OAuth (run separately)
ALTER TABLE users ADD COLUMN google_id VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD UNIQUE INDEX idx_google_id (google_id);

-- 6. Token top-up: allow NULL package_id for custom purchases
ALTER TABLE token_orders MODIFY COLUMN package_id INT DEFAULT NULL;
