-- Migration: Client Meta Credentials (Multi-tenant)
-- Version: 004
-- Date: 2026-01-28
-- Description: Adds Meta credentials per client for full multi-tenant support

-- Add Meta credential columns to clients table
ALTER TABLE `clients`
    ADD COLUMN `meta_app_id` VARCHAR(50) NULL AFTER `description`,
    ADD COLUMN `meta_app_secret_encrypted` TEXT NULL AFTER `meta_app_id`,
    ADD COLUMN `meta_access_token_encrypted` TEXT NULL AFTER `meta_app_secret_encrypted`,
    ADD COLUMN `meta_account_id` VARCHAR(50) NULL COMMENT 'Default ad account ID (act_xxx)' AFTER `meta_access_token_encrypted`,
    ADD COLUMN `meta_token_expires_at` DATETIME NULL AFTER `meta_account_id`,
    ADD COLUMN `meta_connected_at` DATETIME NULL AFTER `meta_token_expires_at`,
    ADD COLUMN `meta_connection_status` ENUM('disconnected','connected','expired','error') DEFAULT 'disconnected' AFTER `meta_connected_at`,
    ADD COLUMN `meta_last_error` TEXT NULL AFTER `meta_connection_status`;

-- Update leads table to reference client instead of user for multi-tenant
ALTER TABLE `leads`
    ADD COLUMN `client_id` INT UNSIGNED NULL AFTER `user_id`,
    ADD KEY `idx_leads_client` (`client_id`),
    ADD CONSTRAINT `fk_leads_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL;

-- Update lead_forms table to reference client
ALTER TABLE `lead_forms`
    ADD COLUMN `client_id` INT UNSIGNED NULL AFTER `user_id`,
    ADD KEY `idx_lead_forms_client` (`client_id`),
    ADD CONSTRAINT `fk_lead_forms_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE SET NULL;

-- Update webhook_subscriptions to reference client
ALTER TABLE `webhook_subscriptions`
    ADD COLUMN `client_id` INT UNSIGNED NULL AFTER `user_id`,
    ADD KEY `idx_webhook_subs_client` (`client_id`),
    ADD CONSTRAINT `fk_webhook_subs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;

-- Update lead_sync_jobs to reference client
ALTER TABLE `lead_sync_jobs`
    ADD COLUMN `client_id` INT UNSIGNED NULL AFTER `user_id`,
    ADD KEY `idx_lead_sync_jobs_client` (`client_id`),
    ADD CONSTRAINT `fk_lead_sync_jobs_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE;

-- Create table for client webhook verification tokens (each client/app has its own)
CREATE TABLE IF NOT EXISTS `client_webhook_tokens` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` INT UNSIGNED NOT NULL,
    `verify_token` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_client_webhook_client` (`client_id`),
    CONSTRAINT `fk_client_webhook_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
