-- PHASE 2 - DATABASE
CREATE TABLE IF NOT EXISTS websites (
 id INT AUTO_INCREMENT PRIMARY KEY,
 workspace_id INT DEFAULT 1,
 name VARCHAR(255) NOT NULL,
 url VARCHAR(500) NOT NULL,
 category VARCHAR(100),
 status ENUM('active','inactive') DEFAULT 'active',
 last_scan DATETIME NULL,
 last_success DATETIME NULL,
 discovered_count INT DEFAULT 0,
 new_count INT DEFAULT 0,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_workspace (workspace_id),
 INDEX idx_status (status)
);

CREATE TABLE IF NOT EXISTS content_sources (
 id INT AUTO_INCREMENT PRIMARY KEY,
 website_id INT NOT NULL,
 source_type ENUM('rss','sitemap','sitemap_index') NOT NULL,
 url VARCHAR(1000) NOT NULL,
 status ENUM('active','inactive','error') DEFAULT 'active',
 last_scan DATETIME NULL,
 last_success DATETIME NULL,
 error_text TEXT NULL,
 discovered_count INT DEFAULT 0,
 new_count INT DEFAULT 0,
 priority INT DEFAULT 5,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_website (website_id),
 INDEX idx_type (source_type),
 FOREIGN KEY (website_id) REFERENCES websites(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS link_library (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 website_id INT NOT NULL,
 source_id INT NULL,
 url VARCHAR(2000) NOT NULL,
 canonical_url VARCHAR(2000) NOT NULL,
 title VARCHAR(500),
 description TEXT,
 image_url VARCHAR(1000),
 content_hash VARCHAR(64),
 status ENUM('discovered','pending','approved','queued','published','skipped','failed','archived') DEFAULT 'discovered',
 first_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
 last_seen DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 published_date DATETIME NULL,
 INDEX idx_canonical (canonical_url(191)),
 INDEX idx_hash (content_hash),
 INDEX idx_website (website_id),
 INDEX idx_status (status),
 UNIQUE KEY uniq_canonical_website (canonical_url(150), website_id)
);

CREATE TABLE IF NOT EXISTS social_accounts (
 id INT AUTO_INCREMENT PRIMARY KEY,
 platform ENUM('facebook','instagram','linkedin','twitter','pinterest','tiktok','youtube','threads') NOT NULL,
 name VARCHAR(255),
 access_token TEXT,
 refresh_token TEXT,
 token_expires DATETIME NULL,
 status ENUM('connected','expired','error') DEFAULT 'connected',
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS social_pages (
 id INT AUTO_INCREMENT PRIMARY KEY,
 account_id INT NOT NULL,
 platform ENUM('facebook','instagram','linkedin','twitter','pinterest','tiktok','youtube','threads') NOT NULL,
 page_id VARCHAR(255) NOT NULL,
 page_name VARCHAR(255),
 access_token TEXT,
 settings JSON,
 auto_publish TINYINT(1) DEFAULT 1,
 posting_interval INT DEFAULT 90,
 daily_limit INT DEFAULT 5,
 active_hours_start TIME DEFAULT '09:00:00',
 active_hours_end TIME DEFAULT '22:00:00',
 timezone VARCHAR(50) DEFAULT 'UTC',
 publishing_mode ENUM('sequential','random','newest_first','oldest_first','ai_optimized') DEFAULT 'random',
 require_approval TINYINT(1) DEFAULT 0,
 current_cycle_id INT NULL,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_account (account_id),
 FOREIGN KEY (account_id) REFERENCES social_accounts(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS content_pool (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 website_id INT NOT NULL,
 link_id BIGINT NOT NULL,
 status ENUM('discovered','pending','approved','queued','published','skipped','failed','archived') DEFAULT 'pending',
 priority ENUM('low','normal','high','new') DEFAULT 'normal',
 age_hours INT NULL,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_website_status (website_id, status),
 FOREIGN KEY (link_id) REFERENCES link_library(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS publish_history (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 account_id INT NOT NULL,
 page_id INT NOT NULL,
 content_id BIGINT NULL,
 link_id BIGINT NULL,
 url VARCHAR(2000) NOT NULL,
 canonical_url VARCHAR(2000) NOT NULL,
 platform VARCHAR(50),
 post_id VARCHAR(255) NULL,
 status ENUM('success','failed','skipped') DEFAULT 'success',
 error_text TEXT NULL,
 cycle_id INT NOT NULL,
 published_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_page_canonical (page_id, canonical_url(150)),
 INDEX idx_cycle (cycle_id),
 INDEX idx_page (page_id),
 INDEX idx_published (published_at)
);

CREATE TABLE IF NOT EXISTS cycles (
 id INT AUTO_INCREMENT PRIMARY KEY,
 page_id INT NOT NULL,
 cycle_number INT NOT NULL,
 status ENUM('active','completed','exhausted') DEFAULT 'active',
 total_links INT DEFAULT 0,
 published_count INT DEFAULT 0,
 started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 completed_at DATETIME NULL,
 INDEX idx_page_cycle (page_id, cycle_number)
);

CREATE TABLE IF NOT EXISTS publish_queue (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 page_id INT NOT NULL,
 link_id BIGINT NOT NULL,
 account_id INT NOT NULL,
 status ENUM('pending','processing','success','failed','retry','cancelled','dead_letter') DEFAULT 'pending',
 attempts INT DEFAULT 0,
 next_retry DATETIME NULL,
 payload JSON,
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_status_next (status, next_retry),
 INDEX idx_page_status (page_id, status),
 UNIQUE KEY uniq_page_link_pending (page_id, link_id, status)
);

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NULL,
 action VARCHAR(100),
 entity_type VARCHAR(100),
 entity_id VARCHAR(100),
 details JSON,
 ip VARCHAR(45),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_action (action)
);