CREATE TABLE users (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 email VARCHAR(190) NOT NULL,
 password_hash VARCHAR(255) NOT NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE social_accounts (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 platform ENUM('facebook','instagram','tiktok') NOT NULL,
 account_name VARCHAR(190) DEFAULT NULL,
 external_id VARCHAR(190) DEFAULT NULL,
 access_token TEXT DEFAULT NULL,
 refresh_token TEXT DEFAULT NULL,
 token_expires_at DATETIME DEFAULT NULL,
 status ENUM('disconnected','connected','expired','error') NOT NULL DEFAULT 'disconnected',
 created_at DATETIME NOT NULL,
 updated_at DATETIME DEFAULT NULL,
 PRIMARY KEY (id),
 UNIQUE KEY uq_platform (platform)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE posts (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 title VARCHAR(160) NOT NULL,
 body TEXT NOT NULL,
 media_path VARCHAR(500) DEFAULT NULL,
 status ENUM('draft','scheduled','queued','processing','published','failed') NOT NULL DEFAULT 'draft',
 scheduled_at DATETIME DEFAULT NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME DEFAULT NULL,
 PRIMARY KEY (id),
 KEY idx_posts_status_schedule (status, scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE post_targets (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 post_id BIGINT UNSIGNED NOT NULL,
 platform ENUM('facebook','instagram','tiktok') NOT NULL,
 destination_id VARCHAR(190) DEFAULT NULL,
 custom_text TEXT DEFAULT NULL,
 status ENUM('pending','processing','published','failed','assisted') NOT NULL DEFAULT 'pending',
 external_post_id VARCHAR(190) DEFAULT NULL,
 error_message TEXT DEFAULT NULL,
 published_at DATETIME DEFAULT NULL,
 PRIMARY KEY (id),
 KEY idx_target_status (status),
 CONSTRAINT fk_target_post FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE facebook_groups (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 group_name VARCHAR(190) NOT NULL,
 group_url VARCHAR(500) DEFAULT NULL,
 notes TEXT DEFAULT NULL,
 status ENUM('active','inactive') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE publish_queue (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 post_target_id BIGINT UNSIGNED NOT NULL,
 run_at DATETIME NOT NULL,
 status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 locked_at DATETIME DEFAULT NULL,
 last_error TEXT DEFAULT NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY (id),
 KEY idx_queue_run (status, run_at),
 CONSTRAINT fk_queue_target FOREIGN KEY (post_target_id) REFERENCES post_targets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE publish_logs (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 post_target_id BIGINT UNSIGNED DEFAULT NULL,
 level ENUM('info','success','warning','error') NOT NULL DEFAULT 'info',
 message TEXT NOT NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY (id),
 KEY idx_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
 setting_key VARCHAR(100) NOT NULL,
 setting_value TEXT DEFAULT NULL,
 updated_at DATETIME DEFAULT NULL,
 PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;