CREATE TABLE `contact_submissions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`email` varchar(320) NOT NULL,
	`company` varchar(255),
	`industry` varchar(100),
	`message` text NOT NULL,
	`status` enum('new','read','replied','archived') NOT NULL DEFAULT 'new',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `contact_submissions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `content_audit` (
	`id` int AUTO_INCREMENT NOT NULL,
	`content_id` int NOT NULL,
	`action` varchar(64) NOT NULL,
	`actor_id` int,
	`note` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `content_audit_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `content_items` (
	`id` int AUTO_INCREMENT NOT NULL,
	`type` enum('article','product','principal','job') NOT NULL,
	`slug` varchar(255) NOT NULL,
	`status` enum('draft','pending_review','approved','published','rejected') NOT NULL DEFAULT 'draft',
	`title_en` varchar(512) NOT NULL,
	`title_ar` varchar(512),
	`body_en` text,
	`body_ar` text,
	`excerpt_en` varchar(1024),
	`excerpt_ar` varchar(1024),
	`meta_title` varchar(512),
	`meta_description` varchar(1024),
	`og_image` varchar(1024),
	`canonical_url` varchar(1024),
	`hero_image` varchar(1024),
	`data_json` text,
	`created_by` int,
	`reviewed_by` int,
	`review_note` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`published_at` timestamp,
	CONSTRAINT `content_items_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`email` varchar(320) NOT NULL,
	`password_hash` text,
	`name` text,
	`login_method` varchar(64) DEFAULT 'password',
	`role` enum('user','editor','admin') NOT NULL DEFAULT 'user',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`last_signed_in` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE INDEX `idx_content_status_type` ON `content_items` (`status`,`type`);--> statement-breakpoint
CREATE INDEX `idx_content_slug` ON `content_items` (`slug`);