ALTER TABLE projects ADD COLUMN platforms_json JSON NULL AFTER platform_key;

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;
