CREATE DATABASE IF NOT EXISTS futurefilmstars CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE futurefilmstars;

CREATE TABLE users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('talent','industry','admin') NOT NULL DEFAULT 'talent',
 city VARCHAR(120) NULL,
 primary_role VARCHAR(120) NULL,
 bio TEXT NULL,
 profile_photo VARCHAR(255) NULL,
 phone VARCHAR(40) NULL,
 whatsapp VARCHAR(40) NULL,
 subscription_status ENUM('trial','active','expired','pending') NOT NULL DEFAULT 'trial',
 trial_started_at DATETIME NULL,
 trial_ends_at DATETIME NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE talent_profiles (
 user_id INT UNSIGNED PRIMARY KEY,
 profile_visibility ENUM('public','members','private') NOT NULL DEFAULT 'public',
 contact_visibility ENUM('public','members','request','private') NOT NULL DEFAULT 'request',
 youtube VARCHAR(500) NULL, instagram VARCHAR(500) NULL, facebook VARCHAR(500) NULL,
 linkedin VARCHAR(500) NULL, website VARCHAR(500) NULL,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE gallery (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 path VARCHAR(255) NOT NULL,
 caption VARCHAR(255) NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id INT UNSIGNED NOT NULL,
 plan ENUM('monthly','quarterly','yearly') NOT NULL,
 amount INT UNSIGNED NOT NULL,
 status ENUM('pending','paid','failed','cancelled') NOT NULL DEFAULT 'pending',
 gateway VARCHAR(50) NULL,
 gateway_reference VARCHAR(190) NULL,
 starts_at DATETIME NULL,
 ends_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE offers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 sender_id INT UNSIGNED NOT NULL,
 talent_id INT UNSIGNED NOT NULL,
 title VARCHAR(200) NOT NULL,
 description TEXT NOT NULL,
 budget VARCHAR(120) NULL,
 status ENUM('pending','accepted','declined','withdrawn') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (talent_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE chat_threads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_one INT UNSIGNED NOT NULL,
 user_two INT UNSIGNED NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uniq_pair (user_one,user_two),
 FOREIGN KEY (user_one) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (user_two) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 thread_id BIGINT UNSIGNED NOT NULL,
 sender_id INT UNSIGNED NOT NULL,
 body TEXT NOT NULL,
 read_at DATETIME NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (thread_id) REFERENCES chat_threads(id) ON DELETE CASCADE,
 FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
 INDEX idx_thread_created (thread_id,created_at)
) ENGINE=InnoDB;

CREATE TABLE contact_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 requester_id INT UNSIGNED NOT NULL,
 talent_id INT UNSIGNED NOT NULL,
 status ENUM('pending','approved','declined') NOT NULL DEFAULT 'pending',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uniq_request (requester_id,talent_id),
 FOREIGN KEY (requester_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (talent_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;