-- Create database
CREATE DATABASE IF NOT EXISTS `free_items_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `free_items_db`;

-- Users table
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(15) NOT NULL,
  `district` varchar(50) NOT NULL,
  `city` varchar(100) NOT NULL,
  `avatar` text DEFAULT NULL,
  `role` enum('user','admin') NOT NULL DEFAULT 'user',
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `emailVerified` tinyint(1) NOT NULL DEFAULT 0,
  `lastLogin` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_users_email` (`email`),
  KEY `idx_users_district` (`district`),
  KEY `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Items table
CREATE TABLE `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `category` varchar(50) NOT NULL,
  `condition` varchar(20) NOT NULL,
  `images` json DEFAULT NULL,
  `ownerId` int(11) NOT NULL,
  `district` varchar(50) NOT NULL,
  `city` varchar(100) NOT NULL,
  `status` enum('available','pending','given_away','expired') NOT NULL DEFAULT 'available',
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `views` int(11) NOT NULL DEFAULT 0,
  `givenToId` int(11) DEFAULT NULL,
  `givenAt` datetime DEFAULT NULL,
  `expiresAt` datetime NOT NULL,
  `tags` json DEFAULT NULL,
  `pickupInstructions` text DEFAULT NULL,
  `preferredContactMethod` enum('email','phone','both') NOT NULL DEFAULT 'both',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_items_ownerId` (`ownerId`),
  KEY `idx_items_district` (`district`),
  KEY `idx_items_category` (`category`),
  KEY `idx_items_status` (`status`),
  KEY `idx_items_isActive` (`isActive`),
  KEY `idx_items_created_at` (`created_at`),
  KEY `idx_items_expiresAt` (`expiresAt`),
  KEY `idx_items_district_category_status` (`district`,`category`,`status`),
  KEY `idx_items_status_isActive_expiresAt` (`status`,`isActive`,`expiresAt`),
  KEY `idx_items_givenToId` (`givenToId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Item interests table
CREATE TABLE `item_interests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `itemId` int(11) NOT NULL,
  `userId` int(11) NOT NULL,
  `message` text DEFAULT NULL,
  `contactedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_item_interests_itemId` (`itemId`),
  KEY `idx_item_interests_userId` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign key constraints
ALTER TABLE `items` 
  ADD CONSTRAINT `fk_items_owner` FOREIGN KEY (`ownerId`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_items_givenTo` FOREIGN KEY (`givenToId`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `item_interests` 
  ADD CONSTRAINT `fk_item_interests_item` FOREIGN KEY (`itemId`) REFERENCES `items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_item_interests_user` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE;