SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS smtp_settings (
    workspace_id BIGINT UNSIGNED PRIMARY KEY,
    host VARCHAR(255) NOT NULL,
    port INT UNSIGNED NOT NULL DEFAULT 587,
    encryption ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
    from_email VARCHAR(255) NOT NULL,
    from_name VARCHAR(180) NULL,
    credentials_encrypted LONGTEXT NULL,
    status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_smtp_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Compatibilidade de instalações V3/V4 onde estas colunas podem faltar.
ALTER TABLE link_clicks ADD COLUMN IF NOT EXISTS scheduled_post_id BIGINT UNSIGNED NULL;
ALTER TABLE scheduled_posts ADD COLUMN IF NOT EXISTS content_variant_id BIGINT UNSIGNED NULL;
ALTER TABLE media_library ADD COLUMN IF NOT EXISTS sha256 CHAR(64) NULL;
ALTER TABLE media_library ADD COLUMN IF NOT EXISTS mime_type VARCHAR(120) NULL;
ALTER TABLE media_library ADD COLUMN IF NOT EXISTS file_size BIGINT UNSIGNED NULL;
ALTER TABLE media_library ADD COLUMN IF NOT EXISTS width INT UNSIGNED NULL;
ALTER TABLE media_library ADD COLUMN IF NOT EXISTS height INT UNSIGNED NULL;

CREATE INDEX IF NOT EXISTS idx_media_sha ON media_library(workspace_id,sha256);
