-- Casa & Style CRM Database Schema

CREATE DATABASE IF NOT EXISTS `crm_casa_style` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `crm_casa_style`;

-- Users Table
CREATE TABLE `users` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'user') DEFAULT 'user',
  `active` BOOLEAN DEFAULT TRUE,
  `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;

-- Categories Table
CREATE TABLE `categories` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `color` VARCHAR(7) DEFAULT '#4A8FD4',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Products Table
CREATE TABLE `products` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `category_id` INT NOT NULL,
  `price` DECIMAL(10, 2) NOT NULL,
  `cost` DECIMAL(10, 2) NOT NULL,
  `stock` INT DEFAULT 0,
  `min_stock` INT DEFAULT 5,
  `sku` VARCHAR(100) UNIQUE,
  `image_path` VARCHAR(500),
  `active` BOOLEAN DEFAULT TRUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Statuses Table
CREATE TABLE `order_statuses` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(7) DEFAULT '#4A8FD4',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Client Statuses Table
CREATE TABLE `client_statuses` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(7) DEFAULT '#3DB87A',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clients Table
CREATE TABLE `clients` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `first_name` VARCHAR(255) NOT NULL,
  `last_name` VARCHAR(255),
  `phone` VARCHAR(20),
  `email` VARCHAR(255),
  `address` TEXT,
  `status_id` INT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`status_id`) REFERENCES `client_statuses`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Discounts Table
CREATE TABLE `discounts` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `type` ENUM('percent', 'fixed') DEFAULT 'percent',
  `value` DECIMAL(10, 2) NOT NULL,
  `valid_from` DATE,
  `valid_to` DATE,
  `active` BOOLEAN DEFAULT TRUE,
  `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;

-- Orders Table
CREATE TABLE `orders` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `client_id` INT NOT NULL,
  `discount_id` INT,
  `status_id` INT NOT NULL DEFAULT 1,
  `delivery_enabled` BOOLEAN DEFAULT FALSE,
  `delivery_price` DECIMAL(10, 2) DEFAULT 0,
  `delivery_address` TEXT,
  `notes` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `order_date` DATE DEFAULT CURRENT_DATE,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`discount_id`) REFERENCES `discounts`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`status_id`) REFERENCES `order_statuses`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Items Table
CREATE TABLE `order_items` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `quantity` INT NOT NULL DEFAULT 1,
  `price` DECIMAL(10, 2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Order Comments Table
CREATE TABLE `order_comments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `order_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `comment` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Product Comments/Notes Table
CREATE TABLE `product_comments` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `product_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `comment` TEXT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Stock History Table
CREATE TABLE `stock_history` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `product_id` INT NOT NULL,
  `type` ENUM('purchase', 'sale', 'adjustment') DEFAULT 'adjustment',
  `quantity_change` INT NOT NULL,
  `reason` VARCHAR(255),
  `user_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Audit Log Table
CREATE TABLE `audit_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `user_id` INT,
  `action` VARCHAR(50) NOT NULL,
  `entity_type` VARCHAR(50) NOT NULL,
  `entity_id` INT,
  `entity_name` VARCHAR(500),
  `old_values` JSON,
  `new_values` JSON,
  `ip_address` VARCHAR(45),
  `user_agent` VARCHAR(500),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at),
  INDEX idx_entity (entity_type, entity_id),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backup Logs Table
CREATE TABLE `backup_logs` (
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `backup_file` VARCHAR(500) NOT NULL,
  `backup_size` BIGINT,
  `backup_type` VARCHAR(50),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default data

-- Note: No default user created for security reasons.
-- Please create your first admin account by visiting: /register.php

-- Default categories
INSERT INTO `categories` (`name`, `color`) VALUES 
('Farfurii', '#D4694A'),
('Pahare', '#4A8FD4'),
('Tacâmuri', '#3DB87A'),
('Oale & tigăi', '#9B7DD4'),
('Accesorii', '#E8C24A');

-- Default order statuses
INSERT INTO `order_statuses` (`name`, `color`) VALUES 
('Nouă', '#4A8FD4'),
('Procesare', '#E8C24A'),
('Expediată', '#9B7DD4'),
('Livrată', '#3DB87A'),
('Rambursată', '#D4694A'),
('Anulată', '#E05252');

-- Default client statuses
INSERT INTO `client_statuses` (`name`, `color`) VALUES 
('Activ', '#3DB87A'),
('Inactiv', '#9A9590'),
('Blocat', '#E05252');

-- Default products
INSERT INTO `products` (`name`, `description`, `category_id`, `price`, `cost`, `stock`, `min_stock`, `sku`, `active`) VALUES 
('Set farfurii ceramică albă', 'Set 6 farfurii adânci, ceramică premium', 1, 189.00, 90.00, 15, 5, 'FAR-001', TRUE),
('Pahare cristal 6buc', 'Pahare vin roșu, cristal boem', 2, 145.00, 60.00, 8, 3, 'PAH-001', TRUE),
('Set tacâmuri inox 24buc', '24 piese inox satinat', 3, 220.00, 100.00, 12, 4, 'TAC-001', TRUE),
('Tigaie granit 28cm', 'Anti-aderentă, granit negru', 4, 175.00, 80.00, 6, 2, 'TIG-001', TRUE),
('Set castroane salată 3buc', 'Sticlă borosilicată', 1, 95.00, 40.00, 20, 5, 'CAS-001', TRUE);

-- Default discounts
INSERT INTO `discounts` (`name`, `type`, `value`, `valid_from`, `valid_to`, `active`) VALUES 
('Reducere de bun venit', 'percent', 10.00, '2024-01-01', '2026-12-31', TRUE),
('Reducere client fidel', 'fixed', 30.00, '2024-01-01', '2026-12-31', TRUE);
