-- WS AI Post - Database installer (MySQL 8)
-- Run this in your target database (created beforehand)

-- Tables
CREATE TABLE IF NOT EXISTS users (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	name VARCHAR(120) NOT NULL,
	email VARCHAR(190) NOT NULL UNIQUE,
	password_hash VARCHAR(255) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_settings (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	user_id INT UNSIGNED NOT NULL,
	openai_api_key TEXT NULL,
	openai_model VARCHAR(100) NOT NULL DEFAULT 'gpt-4.1-mini',
	temperature DECIMAL(3,2) NOT NULL DEFAULT 0.80,
	dalle_enabled TINYINT(1) NOT NULL DEFAULT 0,
	dalle_size VARCHAR(20) NOT NULL DEFAULT '1024x1024',
	auto_generate TINYINT(1) NOT NULL DEFAULT 0,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX(user_id),
	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pages (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	user_id INT UNSIGNED NOT NULL,
	page_name VARCHAR(160) NOT NULL,
	page_description TEXT NULL,
	page_industry VARCHAR(120) NULL,
	tone_style JSON NULL,
	hashtags_default JSON NULL,
	image_style JSON NULL,
	feed_token VARCHAR(64) NOT NULL UNIQUE,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX(user_id),
	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posting_windows (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	page_id INT UNSIGNED NOT NULL,
	dow TINYINT UNSIGNED NOT NULL,
	start_time TIME NOT NULL,
	end_time TIME NOT NULL,
	INDEX(page_id),
	INDEX(dow),
	FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS external_sources (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	page_id INT UNSIGNED NOT NULL,
	source_name VARCHAR(160) NOT NULL,
	feed_url VARCHAR(500) NOT NULL,
	is_active TINYINT(1) NOT NULL DEFAULT 1,
	last_fetched_at DATETIME NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX(page_id),
	FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS posts (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	page_id INT UNSIGNED NOT NULL,
	status ENUM('draft','active','deleted','scheduled','published') NOT NULL DEFAULT 'draft',
	source_type ENUM('generated','imported') NOT NULL DEFAULT 'generated',
	source_ref VARCHAR(255) NULL,
	title VARCHAR(255) NULL,
	body MEDIUMTEXT NULL,
	image_url VARCHAR(512) NULL,
	scheduled_at DATETIME NULL,
	published_at DATETIME NULL,
	ai_params JSON NULL,
	tags JSON NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	INDEX(page_id),
	INDEX(status),
	INDEX(scheduled_at),
	FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS post_generation_log (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	page_id INT UNSIGNED NOT NULL,
	inputs JSON NOT NULL,
	outputs JSON NOT NULL,
	error TEXT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	INDEX(page_id),
	FOREIGN KEY (page_id) REFERENCES pages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
	id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	user_id INT UNSIGNED NOT NULL,
	token VARCHAR(64) NOT NULL UNIQUE,
	ip VARCHAR(45) NULL,
	user_agent VARCHAR(255) NULL,
	expires_at DATETIME NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	INDEX(user_id),
	FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed demo admin user
INSERT INTO users(name, email, password_hash)
SELECT 'Administrator', 'admin@example.com', '$2y$10$K8vEi4Hk9qv9P3Zf2mRrJeQWw2Q0QpN9mU7cJ5K3H2uQ1S3t0zFwe'
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email = 'admin@example.com');

-- Ensure user_settings for demo user
INSERT INTO user_settings(user_id)
SELECT u.id FROM users u
LEFT JOIN user_settings s ON s.user_id = u.id
WHERE u.email = 'admin@example.com' AND s.user_id IS NULL;

-- Seed demo page with a fixed token
INSERT INTO pages(user_id, page_name, page_description, page_industry, tone_style, hashtags_default, image_style, feed_token)
SELECT u.id, 'Demo Business', 'Servicii demo de marketing digital', 'Marketing',
       JSON_OBJECT('tone','prietenos','style','concise'),
       JSON_ARRAY('#marketing','#promotii','#socialmedia'),
       JSON_OBJECT('style','flat','vibe','modern'),
       'demo-token-1234567890abcdef'
FROM users u
LEFT JOIN pages p ON p.user_id = u.id
WHERE u.email = 'admin@example.com' AND p.user_id IS NULL;

-- Seed one posting window (Monday 09:00-11:00)
INSERT INTO posting_windows(page_id, dow, start_time, end_time)
SELECT p.id, 1, '09:00:00', '11:00:00' FROM pages p
WHERE p.user_id = (SELECT id FROM users WHERE email = 'admin@example.com' LIMIT 1)
  AND NOT EXISTS (SELECT 1 FROM posting_windows w WHERE w.page_id = p.id);
