SET NAMES utf8mb4;
SET time_zone = '+08:00';

CREATE TABLE clients (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL, code VARCHAR(40) NULL UNIQUE,
 email VARCHAR(190) NULL, phone VARCHAR(60) NULL, status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NULL, name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','staff','client') NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_users_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, setting_group VARCHAR(60) NOT NULL, setting_key VARCHAR(100) NOT NULL,
 label VARCHAR(180) NOT NULL, value TEXT NULL, sort_order INT NOT NULL DEFAULT 0, active TINYINT(1) NOT NULL DEFAULT 1,
 meta_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_setting (setting_group, setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE projects (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL, title VARCHAR(220) NOT NULL,
 campaign VARCHAR(220) NULL, platform_key VARCHAR(100) NULL, platforms_json JSON NULL, content_type_key VARCHAR(100) NULL, service_type_key VARCHAR(100) NULL,
 status_key VARCHAR(100) NULL, original_url TEXT NULL, start_date DATE NULL, end_date DATE NULL, budget DECIMAL(12,2) NULL,
 notes TEXT NULL, custom_data JSON NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_projects_filter (client_id,platform_key,content_type_key,status_key),
 CONSTRAINT fk_projects_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
 CONSTRAINT fk_projects_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tracking_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NULL, client_id BIGINT UNSIGNED NULL, content_item_id BIGINT UNSIGNED NULL, slug VARCHAR(100) NOT NULL UNIQUE,
 destination_url TEXT NOT NULL, source VARCHAR(120) NULL, medium VARCHAR(120) NULL, campaign VARCHAR(180) NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, expires_at DATETIME NULL, created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_links_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
 CONSTRAINT fk_links_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clicks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tracking_link_id BIGINT UNSIGNED NOT NULL, clicked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 ip_hash CHAR(64) NOT NULL, visitor_hash CHAR(64) NOT NULL, is_unique TINYINT(1) NOT NULL DEFAULT 0,
 user_agent TEXT NULL, device VARCHAR(30) NULL, browser VARCHAR(50) NULL, referrer TEXT NULL, source VARCHAR(120) NULL,
 country VARCHAR(100) NULL, region VARCHAR(150) NULL, INDEX idx_click_time (tracking_link_id,clicked_at),
 INDEX idx_unique (tracking_link_id,visitor_hash,clicked_at),
 CONSTRAINT fk_click_link FOREIGN KEY (tracking_link_id) REFERENCES tracking_links(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE performance_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL, content_item_id BIGINT UNSIGNED NULL, metric_date DATE NOT NULL,
 reach BIGINT NOT NULL DEFAULT 0, impressions BIGINT NOT NULL DEFAULT 0, views BIGINT NOT NULL DEFAULT 0,
 likes BIGINT NOT NULL DEFAULT 0, comments BIGINT NOT NULL DEFAULT 0, shares BIGINT NOT NULL DEFAULT 0,
 saves BIGINT NOT NULL DEFAULT 0, clicks BIGINT NOT NULL DEFAULT 0, leads BIGINT NOT NULL DEFAULT 0,
 sales DECIMAL(12,2) NOT NULL DEFAULT 0, ad_spend DECIMAL(12,2) NOT NULL DEFAULT 0, notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_content_date (project_id,content_item_id,metric_date),
 CONSTRAINT fk_perf_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
 CONSTRAINT fk_perf_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attachments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL, content_item_id BIGINT UNSIGNED NULL, original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(100) NOT NULL, file_size BIGINT NOT NULL, uploaded_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_files_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
 CONSTRAINT fk_files_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE integrations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, provider VARCHAR(80) NOT NULL, client_id BIGINT UNSIGNED NULL,
 credentials_encrypted TEXT NULL, config_json JSON NULL, status ENUM('disabled','connected','error') NOT NULL DEFAULT 'disabled',
 last_sync_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_integrations_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL, title VARCHAR(220) NOT NULL,
 item_kind ENUM('posting','ads_campaign','video','website') NOT NULL DEFAULT 'posting',
 content_type_key VARCHAR(100) NULL, platforms_json JSON NULL, status_key VARCHAR(100) NULL, original_url TEXT NULL,
 published_url TEXT NULL, scheduled_at DATETIME NULL, published_at DATETIME NULL, campaign_budget DECIMAL(12,2) NULL, caption TEXT NULL, notes TEXT NULL,
 custom_data JSON NULL, created_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_content_project_status (project_id,status_key,published_at),
 CONSTRAINT fk_content_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
 CONSTRAINT fk_content_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE performance_entries ADD CONSTRAINT fk_perf_content FOREIGN KEY (content_item_id) REFERENCES content_items(id) ON DELETE CASCADE;
ALTER TABLE attachments ADD CONSTRAINT fk_files_content FOREIGN KEY (content_item_id) REFERENCES content_items(id) ON DELETE CASCADE;
ALTER TABLE tracking_links ADD CONSTRAINT fk_tracking_content FOREIGN KEY (content_item_id) REFERENCES content_items(id) ON DELETE SET NULL;

CREATE TABLE user_client_access (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, client_id BIGINT UNSIGNED NOT NULL,
 can_access TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_user_client (user_id,client_id),
 CONSTRAINT fk_access_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_access_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, token_hash CHAR(64) NOT NULL UNIQUE, email VARCHAR(190) NULL,
 expires_at DATETIME NOT NULL, used_at DATETIME NULL, used_by BIGINT UNSIGNED NULL, created_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_invite_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_invite_used_by FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_platform_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, content_item_id BIGINT UNSIGNED NOT NULL,
 platform_key VARCHAR(100) NOT NULL, post_url TEXT NOT NULL, sync_status ENUM('manual','pending','connected','error') NOT NULL DEFAULT 'manual',
 last_synced_at DATETIME NULL, last_error TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_content_platform (content_item_id,platform_key),
 CONSTRAINT fk_content_platform_item FOREIGN KEY (content_item_id) REFERENCES content_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE report_shares (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, token CHAR(48) NOT NULL UNIQUE, client_id BIGINT UNSIGNED NOT NULL,
 date_from DATE NOT NULL, date_to DATE NOT NULL, include_financial TINYINT(1) NOT NULL DEFAULT 0,
 consultant_comment TEXT NULL, created_by BIGINT UNSIGNED NOT NULL, expires_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_report_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
 CONSTRAINT fk_report_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE daily_platform_stats (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id BIGINT UNSIGNED NOT NULL,
 stat_date DATE NOT NULL, platform_key VARCHAR(100) NOT NULL, metric_key VARCHAR(50) NOT NULL DEFAULT 'followers',
 metric_value BIGINT NOT NULL DEFAULT 0, notes VARCHAR(255) NULL, entered_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_daily_platform_metric (client_id,stat_date,platform_key,metric_key), INDEX idx_daily_client_date (client_id,stat_date),
 CONSTRAINT fk_daily_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
 CONSTRAINT fk_daily_user FOREIGN KEY (entered_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE content_updates (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  update_date DATE NOT NULL,
  content_kind ENUM('graphic','video') NOT NULL,
  platforms_json JSON NULL,
  entered_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_content_update(client_id,update_date,content_kind),
  INDEX idx_content_update_client_date(client_id,update_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (setting_group,setting_key,label,sort_order) VALUES
('platforms','facebook','Facebook',10),('platforms','instagram','Instagram',20),('platforms','tiktok','TikTok',30),
('platforms','youtube','YouTube',40),('platforms','google','Google',50),('platforms','website','Website',60),
('platforms','xiaohongshu','XiaoHongShu / 小红书',35),('platforms','linkedin','LinkedIn',45),
('platforms','x','X / Twitter',55),('platforms','google-business','Google Business Profile',65),
('platforms','pinterest','Pinterest',75),('platforms','threads','Threads',85),('platforms','whatsapp','WhatsApp',95),
('content_types','static','Static Posting',10),('content_types','carousel','Carousel',20),('content_types','reel','Reel',30),
('content_types','video','Video',40),('content_types','story','Story',50),('content_types','website','Website',60),
('content_types','landing-page','Landing Page',70),('content_types','advertisement','Advertisement',80),
('service_types','social-media','Social Media Management',10),('service_types','video-production','Video Production',20),
('service_types','website','Website',30),('service_types','seo','SEO',40),('service_types','sem','SEM',50),
('service_types','meta-ads','Meta Ads',60),('service_types','google-ads','Google Ads',70),
('statuses','draft','Draft',10),('statuses','in-progress','In Progress',20),('statuses','internal-review','Internal Review',30),
('statuses','sent-client','Sent to Client',40),('statuses','revision','Revision',50),('statuses','approved','Approved',60),
('statuses','scheduled','Scheduled',70),('statuses','published','Published',80),('statuses','completed','Completed',90),
('report_settings','company_name','One Tap Go',10),('report_settings','footer_text','Confidential marketing performance report',20),
('system_settings','default_currency','MYR',10);
