-- Current sql file was generated after introspecting the database
-- If you want to run this migration please uncomment this code before executing migrations

CREATE TABLE `chats` (
    `id` int AUTO_INCREMENT NOT NULL,
    `email` varchar(255) NOT NULL,
    `name` varchar(255) NOT NULL,
    `subject` varchar(255) NOT NULL,
    `message` text NOT NULL,
    `is_read` tinyint(1) NOT NULL DEFAULT 0,
    `is_replied` tinyint(1) NOT NULL DEFAULT 0,
    `created_at` datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP),
    CONSTRAINT `chats_id` PRIMARY KEY (`id`)
);
--> statement-breakpoint
CREATE TABLE `reply` (
    `id` int AUTO_INCREMENT NOT NULL,
    `chat_id` int NOT NULL,
    `reply` text NOT NULL,
    `replied_by` varchar(255) NOT NULL,
    `replied_at` datetime NOT NULL DEFAULT(CURRENT_TIMESTAMP),
    CONSTRAINT `reply_id` PRIMARY KEY (`id`)
);
--> statement-breakpoint
CREATE TABLE `tokens` (
    `token_id` int AUTO_INCREMENT NOT NULL,
    `email` varchar(255) NOT NULL,
    `token` varchar(255) NOT NULL,
    `expired_at` timestamp,
    `generated_at` timestamp NOT NULL DEFAULT(CURRENT_TIMESTAMP),
    CONSTRAINT `tokens_token_id` PRIMARY KEY (`token_id`)
);
--> statement-breakpoint
CREATE TABLE `users` (
    `id` int AUTO_INCREMENT NOT NULL,
    `username` varchar(255) NOT NULL,
    `email` varchar(255) NOT NULL,
    `phone` varchar(15),
    `address` varchar(255),
    `position` varchar(50) NOT NULL DEFAULT 'staff',
    `password` varchar(500) NOT NULL,
    `first_name` varchar(255),
    `last_name` varchar(255),
    `is_admin` tinyint(1),
    `created_at` timestamp NOT NULL DEFAULT(CURRENT_TIMESTAMP),
    `updated_at` timestamp NOT NULL DEFAULT(CURRENT_TIMESTAMP) ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `users_id` PRIMARY KEY (`id`),
    CONSTRAINT `email` UNIQUE (`email`),
    CONSTRAINT `phone` UNIQUE (`phone`),
    CONSTRAINT `username` UNIQUE (`username`)
);
--> statement-breakpoint
ALTER TABLE `reply`
ADD CONSTRAINT `reply_ibfk_1` FOREIGN KEY (`chat_id`) REFERENCES `chats` (`id`) ON DELETE cascade ON UPDATE cascade;
--> statement-breakpoint
ALTER TABLE `tokens`
ADD CONSTRAINT `tokens_ibfk_1` FOREIGN KEY (`email`) REFERENCES `users` (`email`) ON DELETE cascade ON UPDATE cascade;
--> statement-breakpoint
CREATE INDEX `chat_id` ON `reply` (`chat_id`);
--> statement-breakpoint
CREATE INDEX `email` ON `tokens` (`email`);