USE riau_harmoni;

CREATE TABLE IF NOT EXISTS permissions (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL UNIQUE, description VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS role_permissions (role_name VARCHAR(80) NOT NULL, permission_id BIGINT NOT NULL, PRIMARY KEY(role_name, permission_id), FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS user_permissions (user_id BIGINT NOT NULL, permission_id BIGINT NOT NULL, granted TINYINT(1) DEFAULT 1, PRIMARY KEY(user_id, permission_id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS categories (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(120) NOT NULL, slug VARCHAR(140) NOT NULL UNIQUE, description TEXT, parent_id BIGINT, icon VARCHAR(80), color VARCHAR(20), is_active TINYINT(1) DEFAULT 1, sort_order INT DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(parent_id) REFERENCES categories(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS regions (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(120) NOT NULL, slug VARCHAR(140) NOT NULL UNIQUE, type VARCHAR(50) DEFAULT 'kabupaten_kota', parent_id BIGINT, province_code VARCHAR(20), city_code VARCHAR(20), is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(parent_id) REFERENCES regions(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS tags (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(120) NOT NULL, normalized_name VARCHAR(120) NOT NULL UNIQUE, slug VARCHAR(140) NOT NULL UNIQUE, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);

CREATE TABLE IF NOT EXISTS news_clusters (id BIGINT PRIMARY KEY AUTO_INCREMENT, cluster_title VARCHAR(255), primary_topic VARCHAR(120), category_id BIGINT, region_id BIGINT, first_published_at DATETIME, last_updated_at DATETIME, source_count INT DEFAULT 0, credibility_score DECIMAL(5,2) DEFAULT 0, trend_score DECIMAL(8,3) DEFAULT 0, verification_status VARCHAR(40) DEFAULT 'unverified', processing_status VARCHAR(40) DEFAULT 'collecting', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, INDEX idx_cluster_status(processing_status), FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL, FOREIGN KEY(region_id) REFERENCES regions(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS articles (id BIGINT PRIMARY KEY AUTO_INCREMENT, cluster_id BIGINT, author_id BIGINT, headline VARCHAR(255) NOT NULL, slug VARCHAR(280) NOT NULL UNIQUE, summary TEXT, lead TEXT, body_html LONGTEXT, body_markdown LONGTEXT, featured_image_id BIGINT, image_alt_text VARCHAR(255), image_caption TEXT, seo_title VARCHAR(255), meta_description VARCHAR(320), focus_keyword VARCHAR(160), status VARCHAR(40) DEFAULT 'idea', confidence_score DECIMAL(5,2) DEFAULT 0, risk_flags JSON, ai_assisted TINYINT(1) DEFAULT 0, breaking_level VARCHAR(20) DEFAULT 'normal', published_at DATETIME, scheduled_at DATETIME, updated_public_at DATETIME, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, deleted_at DATETIME, INDEX idx_articles_status(status), INDEX idx_articles_published(published_at), INDEX idx_articles_cluster(cluster_id), FOREIGN KEY(cluster_id) REFERENCES news_clusters(id) ON DELETE SET NULL, FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS article_categories (article_id BIGINT NOT NULL, category_id BIGINT NOT NULL, PRIMARY KEY(article_id,category_id), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS article_tags (article_id BIGINT NOT NULL, tag_id BIGINT NOT NULL, PRIMARY KEY(article_id,tag_id), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(tag_id) REFERENCES tags(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS article_regions (article_id BIGINT NOT NULL, region_id BIGINT NOT NULL, PRIMARY KEY(article_id,region_id), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(region_id) REFERENCES regions(id) ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS news_sources (id BIGINT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(180) NOT NULL, source_type VARCHAR(50) NOT NULL, base_url VARCHAR(2048), rss_url VARCHAR(2048), sitemap_url VARCHAR(2048), domain VARCHAR(255), source_category VARCHAR(100), region VARCHAR(120), credibility_score TINYINT UNSIGNED DEFAULT 50, credibility_reason TEXT, is_active TINYINT(1) DEFAULT 0, check_interval_minutes INT DEFAULT 60, fetch_method VARCHAR(40) DEFAULT 'rss', scraping_allowed TINYINT(1) DEFAULT 0, article_selector VARCHAR(255), title_selector VARCHAR(255), content_selector VARCHAR(255), image_selector VARCHAR(255), date_selector VARCHAR(255), author_selector VARCHAR(255), notes TEXT, last_checked_at DATETIME, last_check_status VARCHAR(40), failure_count INT DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, INDEX idx_sources_active(is_active));
CREATE TABLE IF NOT EXISTS news_keywords (id BIGINT PRIMARY KEY AUTO_INCREMENT, keyword VARCHAR(180) NOT NULL, keyword_group VARCHAR(100), category_id BIGINT, region_id BIGINT, priority TINYINT DEFAULT 5, is_active TINYINT(1) DEFAULT 1, exact_match TINYINT(1) DEFAULT 0, negative_keyword TINYINT(1) DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE SET NULL, FOREIGN KEY(region_id) REFERENCES regions(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS raw_news_items (id BIGINT PRIMARY KEY AUTO_INCREMENT, source_id BIGINT NOT NULL, external_id VARCHAR(255), original_url VARCHAR(2048) NOT NULL, canonical_url VARCHAR(2048), title VARCHAR(500), author VARCHAR(255), published_at DATETIME, fetched_at DATETIME, raw_html LONGTEXT, cleaned_text LONGTEXT, excerpt TEXT, image_url VARCHAR(2048), language VARCHAR(20), content_hash CHAR(64), url_hash CHAR(64), processing_status VARCHAR(40) DEFAULT 'discovered', credibility_score DECIMAL(5,2) DEFAULT 0, error_message TEXT, metadata_json JSON, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY uq_source_url_hash(source_id,url_hash), INDEX idx_raw_status(processing_status), INDEX idx_raw_content_hash(content_hash), FOREIGN KEY(source_id) REFERENCES news_sources(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS news_cluster_items (cluster_id BIGINT NOT NULL, raw_news_item_id BIGINT NOT NULL UNIQUE, similarity_score DECIMAL(6,4), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(cluster_id,raw_news_item_id), FOREIGN KEY(cluster_id) REFERENCES news_clusters(id) ON DELETE CASCADE, FOREIGN KEY(raw_news_item_id) REFERENCES raw_news_items(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS article_sources (article_id BIGINT NOT NULL, raw_news_item_id BIGINT NOT NULL, is_primary TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY(article_id,raw_news_item_id), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(raw_news_item_id) REFERENCES raw_news_items(id) ON DELETE RESTRICT);
CREATE TABLE IF NOT EXISTS news_facts (id BIGINT PRIMARY KEY AUTO_INCREMENT, cluster_id BIGINT NOT NULL, fact_type VARCHAR(80), fact_text TEXT NOT NULL, normalized_value VARCHAR(500), confidence_score DECIMAL(5,2), verification_status VARCHAR(40), created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(cluster_id) REFERENCES news_clusters(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS news_fact_sources (fact_id BIGINT NOT NULL, raw_news_item_id BIGINT NOT NULL, source_excerpt TEXT, PRIMARY KEY(fact_id,raw_news_item_id), FOREIGN KEY(fact_id) REFERENCES news_facts(id) ON DELETE CASCADE, FOREIGN KEY(raw_news_item_id) REFERENCES raw_news_items(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS news_conflicts (id BIGINT PRIMARY KEY AUTO_INCREMENT, cluster_id BIGINT NOT NULL, field_name VARCHAR(100), description TEXT, source_values JSON, severity VARCHAR(30) DEFAULT 'material', status VARCHAR(30) DEFAULT 'open', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(cluster_id) REFERENCES news_clusters(id) ON DELETE CASCADE);

CREATE TABLE IF NOT EXISTS media_assets (id BIGINT PRIMARY KEY AUTO_INCREMENT, uploaded_by BIGINT, source_url VARCHAR(2048), local_path VARCHAR(500), license VARCHAR(120), attribution TEXT, photographer VARCHAR(255), alt_text VARCHAR(255), caption TEXT, width INT, height INT, mime_type VARCHAR(120), file_size BIGINT, usage_status VARCHAR(40) DEFAULT 'pending', is_ai_generated TINYINT(1) DEFAULT 0, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(uploaded_by) REFERENCES users(id) ON DELETE SET NULL);
ALTER TABLE articles ADD CONSTRAINT fk_articles_featured_image FOREIGN KEY(featured_image_id) REFERENCES media_assets(id) ON DELETE SET NULL;
CREATE TABLE IF NOT EXISTS article_revisions (id BIGINT PRIMARY KEY AUTO_INCREMENT, article_id BIGINT NOT NULL, version INT NOT NULL, changed_by BIGINT, change_type VARCHAR(50), old_content JSON, new_content JSON, change_summary TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_article_version(article_id,version), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS ai_prompts (id BIGINT PRIMARY KEY AUTO_INCREMENT, prompt_name VARCHAR(120) NOT NULL, prompt_type VARCHAR(80) NOT NULL, system_prompt LONGTEXT NOT NULL, user_prompt_template LONGTEXT NOT NULL, model VARCHAR(120), temperature DECIMAL(4,2), max_tokens INT, version INT DEFAULT 1, is_active TINYINT(1) DEFAULT 1, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY uq_prompt_version(prompt_name,version));
CREATE TABLE IF NOT EXISTS ai_runs (id BIGINT PRIMARY KEY AUTO_INCREMENT, task_type VARCHAR(80), provider VARCHAR(80), model VARCHAR(120), prompt_version INT, input_reference_type VARCHAR(80), input_reference_id BIGINT, request_id VARCHAR(255), input_tokens INT DEFAULT 0, output_tokens INT DEFAULT 0, estimated_cost DECIMAL(12,6) DEFAULT 0, latency_ms INT, status VARCHAR(40), error_message TEXT, response_json JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_ai_runs_created(created_at));
CREATE TABLE IF NOT EXISTS editorial_reviews (id BIGINT PRIMARY KEY AUTO_INCREMENT, article_id BIGINT NOT NULL, reviewer_id BIGINT, action VARCHAR(40), notes TEXT, risk_flags JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(reviewer_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS publication_schedules (id BIGINT PRIMARY KEY AUTO_INCREMENT, article_id BIGINT NOT NULL UNIQUE, scheduled_at DATETIME NOT NULL, status VARCHAR(30) DEFAULT 'pending', created_by BIGINT, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE, FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS breaking_news (id BIGINT PRIMARY KEY AUTO_INCREMENT, article_id BIGINT NOT NULL, level VARCHAR(20) DEFAULT 'important', starts_at DATETIME, ends_at DATETIME, priority INT DEFAULT 0, ticker_text VARCHAR(255), created_by BIGINT, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS social_contents (id BIGINT PRIMARY KEY AUTO_INCREMENT, article_id BIGINT NOT NULL, platform VARCHAR(30), content TEXT, status VARCHAR(30) DEFAULT 'draft', created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL, UNIQUE KEY uq_article_platform(article_id,platform), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS analytics_events (id BIGINT PRIMARY KEY AUTO_INCREMENT, event_type VARCHAR(80), article_id BIGINT, anonymous_session_hash CHAR(64), referrer_domain VARCHAR(255), device_category VARCHAR(40), metadata_json JSON, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_analytics_created(created_at), FOREIGN KEY(article_id) REFERENCES articles(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS system_settings (setting_key VARCHAR(160) PRIMARY KEY, setting_value TEXT, value_type VARCHAR(30) DEFAULT 'string', is_secret TINYINT(1) DEFAULT 0, updated_by BIGINT, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS job_runs (id BIGINT PRIMARY KEY AUTO_INCREMENT, job_type VARCHAR(100), unique_key VARCHAR(255), payload_json JSON, status VARCHAR(30) DEFAULT 'queued', attempts INT DEFAULT 0, available_at DATETIME, locked_at DATETIME, finished_at DATETIME, error_message TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NULL, UNIQUE KEY uq_job_unique(job_type,unique_key), INDEX idx_job_status(status,available_at));
CREATE TABLE IF NOT EXISTS subscribers (id BIGINT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) NOT NULL UNIQUE, status VARCHAR(30) DEFAULT 'pending', verification_token_hash CHAR(64), preferences JSON, subscribed_at DATETIME, unsubscribed_at DATETIME, created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL);