-- HIEN CHAT SOCIAL UPGRADE
-- Chon database scaninfo_hienchat trong phpMyAdmin truoc khi import.
-- Khong tao/xoa database va khong sua/xoa cac bang cu.

CREATE TABLE IF NOT EXISTS hc_posts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 body TEXT NOT NULL,
 visibility ENUM('public','followers') NOT NULL DEFAULT 'public',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NULL DEFAULT NULL,
 INDEX idx_posts_created(created_at), INDEX idx_posts_user(user_id,id),
 CONSTRAINT fk_hcp_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_post_media (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 post_id BIGINT UNSIGNED NOT NULL,
 media_type ENUM('image','video') NOT NULL,
 file_path VARCHAR(500) NOT NULL,
 mime_type VARCHAR(100) NOT NULL,
 file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_media_post(post_id),
 CONSTRAINT fk_hcpm_post FOREIGN KEY(post_id) REFERENCES hc_posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_follows (
 follower_id BIGINT UNSIGNED NOT NULL,
 followed_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(follower_id,followed_id), INDEX idx_followed(followed_id),
 CONSTRAINT fk_hcf_follower FOREIGN KEY(follower_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_hcf_followed FOREIGN KEY(followed_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_wallets (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 balance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_hcw_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_wallet_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 type ENUM('topup','gift_sent','gift_received','withdraw','refund','admin_adjust') NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 status ENUM('pending','completed','rejected') NOT NULL DEFAULT 'completed',
 reference_code VARCHAR(100) NULL,
 note VARCHAR(500) NOT NULL DEFAULT '',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_wallet_user(user_id,id), INDEX idx_wallet_ref(reference_code),
 CONSTRAINT fk_hcwt_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_gifts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(80) NOT NULL,
 icon VARCHAR(20) NOT NULL DEFAULT '🎁',
 price DECIMAL(12,2) NOT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_gift_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sender_id BIGINT UNSIGNED NOT NULL,
 receiver_id BIGINT UNSIGNED NOT NULL,
 gift_id BIGINT UNSIGNED NOT NULL,
 quantity INT UNSIGNED NOT NULL DEFAULT 1,
 total_amount DECIMAL(14,2) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_gift_receiver(receiver_id,id),
 CONSTRAINT fk_hcge_sender FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_hcge_receiver FOREIGN KEY(receiver_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_hcge_gift FOREIGN KEY(gift_id) REFERENCES hc_gifts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_topups (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 transfer_code VARCHAR(40) NOT NULL UNIQUE,
 method ENUM('vietqr','gateway') NOT NULL DEFAULT 'vietqr',
 status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
 proof_path VARCHAR(500) NULL,
 admin_note VARCHAR(500) NOT NULL DEFAULT '',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_at DATETIME NULL,
 INDEX idx_topup_status(status,id),
 CONSTRAINT fk_hct_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_withdrawals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 bank_name VARCHAR(100) NOT NULL,
 account_number VARCHAR(50) NOT NULL,
 account_name VARCHAR(120) NOT NULL,
 status ENUM('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
 admin_note VARCHAR(500) NOT NULL DEFAULT '',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_at DATETIME NULL,
 INDEX idx_withdraw_status(status,id),
 CONSTRAINT fk_hcwd_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_admins (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 role ENUM('superadmin','moderator') NOT NULL DEFAULT 'moderator',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_hca_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_live_rooms (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 host_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(120) NOT NULL,
 status ENUM('scheduled','live','ended') NOT NULL DEFAULT 'live',
 provider VARCHAR(40) NOT NULL DEFAULT 'external_pending',
 provider_room_id VARCHAR(190) NULL,
 started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 ended_at DATETIME NULL,
 INDEX idx_live_status(status,started_at),
 CONSTRAINT fk_hclr_host FOREIGN KEY(host_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS hc_reports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 reporter_id BIGINT UNSIGNED NOT NULL,
 target_user_id BIGINT UNSIGNED NULL,
 post_id BIGINT UNSIGNED NULL,
 reason VARCHAR(100) NOT NULL,
 details VARCHAR(1000) NOT NULL DEFAULT '',
 status ENUM('pending','reviewed','dismissed') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_report_status(status,id),
 CONSTRAINT fk_hcr_reporter FOREIGN KEY(reporter_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_hcr_target FOREIGN KEY(target_user_id) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_hcr_post FOREIGN KEY(post_id) REFERENCES hc_posts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO hc_gifts(name,icon,price,sort_order) VALUES
('Trái tim','💜',100,1),('Hoa hồng','🌹',500,2),('Vương miện','👑',2000,3),('Kim cương','💎',5000,4),('Du thuyền','🛥️',20000,5);