CREATE TABLE IF NOT EXISTS content_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, project_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(220) NOT NULL, 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, 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 COLUMN content_item_id BIGINT UNSIGNED NULL AFTER project_id;
ALTER TABLE performance_entries ADD INDEX idx_perf_project (project_id);
ALTER TABLE performance_entries DROP INDEX uq_project_date;
ALTER TABLE performance_entries ADD UNIQUE KEY uq_content_date (project_id,content_item_id,metric_date);
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 COLUMN content_item_id BIGINT UNSIGNED NULL AFTER project_id;
ALTER TABLE attachments ADD CONSTRAINT fk_files_content FOREIGN KEY (content_item_id) REFERENCES content_items(id) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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;

INSERT INTO settings(setting_group,setting_key,label,sort_order) VALUES
('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)
ON DUPLICATE KEY UPDATE label=VALUES(label),active=1;
