SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL, phone VARCHAR(30) NOT NULL UNIQUE,
 email VARCHAR(190) NULL UNIQUE, address VARCHAR(500) NULL,
 password_hash VARCHAR(255) NOT NULL,
 status ENUM('active','blocked','inactive') NOT NULL DEFAULT 'active',
 trial_used TINYINT(1) NOT NULL DEFAULT 0, trial_started_at DATETIME NULL, trial_expires_at DATETIME NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NULL,
 INDEX idx_user_status(status), INDEX idx_user_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_devices (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,
 device_uid VARCHAR(190) NOT NULL,device_name VARCHAR(120) NULL,public_key TEXT NULL,
 status ENUM('active','blocked','pending','unbound') NOT NULL DEFAULT 'active',
 first_seen_at DATETIME NULL,last_seen_at DATETIME NULL,
 UNIQUE KEY uq_user_device(user_id,device_uid),INDEX(user_id,status),
 CONSTRAINT fk_device_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,device_id BIGINT UNSIGNED NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,refresh_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,refresh_expires_at DATETIME NOT NULL,created_at DATETIME NOT NULL,last_used_at DATETIME NULL,revoked_at DATETIME NULL,
 INDEX(user_id),CONSTRAINT fk_session_user FOREIGN KEY(user_id) REFERENCES users(id),
 CONSTRAINT fk_session_device FOREIGN KEY(device_id) REFERENCES user_devices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trial_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,starts_at DATETIME NOT NULL,expires_at DATETIME NOT NULL,
 admin_id BIGINT UNSIGNED NULL,note VARCHAR(255) NULL,created_at DATETIME NOT NULL,INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_plans (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL DEFAULT 0,
 currency VARCHAR(8) NOT NULL DEFAULT 'BDT',duration_days INT NOT NULL DEFAULT 30,description VARCHAR(500) NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,plan_id BIGINT UNSIGNED NULL,
 payment_request_id BIGINT UNSIGNED NULL,status ENUM('active','expired','cancelled') NOT NULL,
 starts_at DATETIME NOT NULL,expires_at DATETIME NOT NULL,created_at DATETIME NOT NULL,created_by BIGINT UNSIGNED NULL,
 INDEX(user_id,status,expires_at),CONSTRAINT fk_sub_user FOREIGN KEY(user_id) REFERENCES users(id),
 CONSTRAINT fk_sub_plan FOREIGN KEY(plan_id) REFERENCES subscription_plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_methods (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(60) NOT NULL,account_type VARCHAR(40) NOT NULL DEFAULT 'Personal',
 account_number VARCHAR(80) NOT NULL,instructions TEXT NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,device_id BIGINT UNSIGNED NULL,
 payment_method_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(10,2) NOT NULL,transaction_last4 CHAR(4) NOT NULL,
 transaction_reference VARCHAR(190) NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 admin_remarks VARCHAR(500) NULL,submitted_at DATETIME NOT NULL,reviewed_at DATETIME NULL,reviewed_by BIGINT UNSIGNED NULL,
 approved_subscription_id BIGINT UNSIGNED NULL,INDEX(status,submitted_at),INDEX(user_id),
 CONSTRAINT fk_pay_user FOREIGN KEY(user_id) REFERENCES users(id),
 CONSTRAINT fk_pay_method FOREIGN KEY(payment_method_id) REFERENCES payment_methods(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS device_replacement_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,current_device_id BIGINT UNSIGNED NULL,
 new_device_uid VARCHAR(190) NOT NULL,new_device_name VARCHAR(120) NULL,status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 admin_remarks VARCHAR(500) NULL,created_at DATETIME NOT NULL,reviewed_at DATETIME NULL,reviewed_by BIGINT UNSIGNED NULL,
 INDEX(status,created_at),CONSTRAINT fk_replace_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,role ENUM('super_admin','manager','support_admin','accountant','moderator') NOT NULL DEFAULT 'support_admin',
 status ENUM('active','blocked') NOT NULL DEFAULT 'active',last_login_at DATETIME NULL,created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_activity_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,admin_id BIGINT UNSIGNED NULL,action VARCHAR(120) NOT NULL,
 details TEXT NULL,ip VARCHAR(45) NULL,created_at DATETIME NOT NULL,INDEX(created_at),INDEX(admin_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS websites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(120) NOT NULL,url VARCHAR(1000) NOT NULL,
 icon_url VARCHAR(1000) NULL,category VARCHAR(80) NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,sort_order INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(120) NOT NULL UNIQUE,
 setting_value TEXT NULL,is_public TINYINT(1) NOT NULL DEFAULT 0,updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,version_name VARCHAR(40) NOT NULL,version_code INT NOT NULL,
 min_version_code INT NOT NULL DEFAULT 1,force_update TINYINT(1) NOT NULL DEFAULT 0,enabled TINYINT(1) NOT NULL DEFAULT 1,
 update_url VARCHAR(1000) NULL,update_message VARCHAR(500) NULL,release_notes TEXT NULL,created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS youtube_api_keys (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,label VARCHAR(100) NOT NULL,api_key VARCHAR(255) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,priority INT NOT NULL DEFAULT 10,success_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
 error_count BIGINT UNSIGNED NOT NULL DEFAULT 0,last_used_at DATETIME NULL,last_http_code SMALLINT NULL,last_error VARCHAR(190) NULL,
 created_at DATETIME NOT NULL,updated_at DATETIME NULL,UNIQUE KEY uq_youtube_key(api_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coupons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(60) NOT NULL UNIQUE,discount_percent DECIMAL(5,2) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,usage_limit INT NULL,used_count INT NOT NULL DEFAULT 0,expires_at DATETIME NULL,created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,message TEXT NOT NULL,
 target_user_id BIGINT UNSIGNED NULL,enabled TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL,INDEX(target_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_tickets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,subject VARCHAR(180) NOT NULL,
 message TEXT NOT NULL,status ENUM('open','answered','closed') NOT NULL DEFAULT 'open',admin_reply TEXT NULL,
 created_at DATETIME NOT NULL,updated_at DATETIME NULL,INDEX(user_id,status),
 CONSTRAINT fk_ticket_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,path VARCHAR(255) NOT NULL,method VARCHAR(10) NOT NULL,
 http_code SMALLINT NOT NULL,ip VARCHAR(45) NULL,created_at DATETIME NOT NULL,INDEX(created_at),INDEX(path)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,identity_hash CHAR(64) NOT NULL,ip VARCHAR(45) NOT NULL,
 attempted_at DATETIME NOT NULL,success TINYINT(1) NOT NULL DEFAULT 0,INDEX(identity_hash,attempted_at),INDEX(ip,attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS database_backups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,file_name VARCHAR(190) NOT NULL,file_size BIGINT UNSIGNED NOT NULL,
 created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS password_resets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,used_at DATETIME NULL,created_at DATETIME NOT NULL,INDEX(user_id),
 CONSTRAINT fk_reset_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS coupon_usages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,coupon_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,
 used_at DATETIME NOT NULL,UNIQUE KEY uq_coupon_user(coupon_id,user_id),
 CONSTRAINT fk_coupon_usage_coupon FOREIGN KEY(coupon_id) REFERENCES coupons(id),
 CONSTRAINT fk_coupon_usage_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS password_resets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,used_at DATETIME NULL,created_at DATETIME NOT NULL,INDEX(user_id),
 CONSTRAINT fk_reset_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS coupon_usages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,coupon_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,
 used_at DATETIME NOT NULL,UNIQUE KEY uq_coupon_user(coupon_id,user_id),
 CONSTRAINT fk_coupon_usage_coupon FOREIGN KEY(coupon_id) REFERENCES coupons(id),
 CONSTRAINT fk_coupon_usage_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SET FOREIGN_KEY_CHECKS=1;
INSERT IGNORE INTO subscription_plans(name,price,currency,duration_days,description,enabled,sort_order) VALUES('Monthly',100,'BDT',30,'30-day YBrowse access',1,1);
INSERT IGNORE INTO app_settings(setting_key,setting_value,is_public) VALUES
('registration_enabled','1',1),('maintenance_mode','0',1),('support_phone','',1),('terms_url','',1),('privacy_url','',1);
INSERT INTO app_versions(version_name,version_code,min_version_code,force_update,enabled,created_at)
SELECT '1.0.0',1,1,0,1,UTC_TIMESTAMP() WHERE NOT EXISTS(SELECT 1 FROM app_versions);
