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

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NOT NULL,
  `role` ENUM('admin', 'manager') DEFAULT 'admin',
  `security_question` VARCHAR(255) NOT NULL,
  `security_answer` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 2. Financial Years Table
CREATE TABLE IF NOT EXISTS `financial_years` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `year_label` VARCHAR(10) NOT NULL UNIQUE, -- e.g., '2026-2027'
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

-- 3. Operators Table
CREATE TABLE IF NOT EXISTS `operators` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `opening_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 4. Products Table
CREATE TABLE IF NOT EXISTS `products` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `size` VARCHAR(50) DEFAULT NULL,
  `purchase_rate` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `opening_stock` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 5. Consumptions Table (Transactional daily records / Job Work)
CREATE TABLE IF NOT EXISTS `consumptions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `operator_id` INT NOT NULL,
  `product_id` INT NOT NULL,
  `issued_qty` INT NOT NULL DEFAULT 0,
  `quantity` INT NOT NULL DEFAULT 0, -- Pure / OK Quantity returned
  `rejected_qty` INT NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `consume_date` DATE NOT NULL,      -- Issue Date
  `return_date` DATE DEFAULT NULL,   -- Return Date
  `remarks` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`operator_id`) REFERENCES `operators` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 6. Raw Material Custom Rates Table
CREATE TABLE IF NOT EXISTS `raw_material_rates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `rate_type` ENUM('consumer', 'product') NOT NULL,
  `entity_id` INT NOT NULL,
  `custom_rate` DECIMAL(10,2) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 7. Audit Logs Table (For security overrides & sensitive edits)
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL,
  `action` VARCHAR(100) NOT NULL,
  `details` TEXT NOT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Indexes for performance tuning
CREATE INDEX `idx_consumptions_operator_date` ON `consumptions` (`operator_id`, `consume_date`);
CREATE INDEX `idx_consumptions_product_date` ON `consumptions` (`product_id`, `consume_date`);
CREATE INDEX `idx_raw_material_rates_entity` ON `raw_material_rates` (`rate_type`, `entity_id`);

-- Seed Data: Financial Years
INSERT INTO `financial_years` (`year_label`, `start_date`, `end_date`, `is_active`) VALUES
('2025-2026', '2025-04-01', '2026-03-31', 1),
('2026-2027', '2026-04-01', '2027-03-31', 1),
('2027-2028', '2027-04-01', '2028-03-31', 1);

-- Seed Data: Default Admin User (username: admin, password: admin123)
-- Hash generated via standard PHP BCRYPT
INSERT INTO `users` (`username`, `password`, `full_name`, `role`, `security_question`, `security_answer`) VALUES
('admin', '$2y$10$Hr2jLTX5cfp4mBNs3xoHDuza1aB4KyyalOmYAKkIpRcMqJDl0WM5y', 'Administrator', 'admin', 'First pet\'s name?', 'rocky');

-- Seed Data: Operators
INSERT INTO `operators` (`id`, `name`, `mobile`, `address`, `opening_balance`) VALUES
(1, 'SAKEEL', '9876543210', 'Mumbai, India', 12000.00),
(2, 'AMIT KUMAR', '9822334455', 'Delhi, India', 8500.00),
(3, 'RAJESH PATEL', '9766554433', 'Gujarat, India', 15000.00),
(4, 'VIKRAM SINGH', '9111222333', 'Rajasthan, India', 0.00),
(5, 'ANIL SHARMA', '9444555666', 'Punjab, India', 9500.00),
(6, 'SANJAY DUTT', '9222333444', 'Maharashtra, India', 5000.00);

-- Seed Data: Products
INSERT INTO `products` (`id`, `name`, `size`, `purchase_rate`, `opening_stock`) VALUES
(1, 'Rotex Solenoid Valve', '1/4 inch', 450.00, 120),
(2, 'Rotex Limit Switch Box', 'Std', 1250.00, 50),
(3, 'Air Filter Regulator', '1/2 inch', 650.00, 80),
(4, 'Pneumatic Cylinder', '50x100', 2200.00, 30),
(5, 'Brass Ball Valve', '1 inch', 350.00, 200),
(6, 'Teflon Seal Kit', 'Std', 75.00, 500),
(7, 'ROTEX Positioner', 'YT-1000', 8500.00, 15);

-- Seed Data: Custom Override Rates
INSERT INTO `raw_material_rates` (`id`, `rate_type`, `entity_id`, `custom_rate`) VALUES
(1, 'consumer', 1, 420.00),
(2, 'consumer', 2, 600.00),
(3, 'product', 2, 1200.00),
(4, 'product', 4, 2100.00);

-- Seed Data: Consumptions
INSERT INTO `consumptions` (`id`, `operator_id`, `product_id`, `issued_qty`, `quantity`, `rejected_qty`, `status`, `consume_date`, `return_date`, `remarks`) VALUES
(1, 1, 1, 5, 5, 0, 'completed', '2025-05-10', '2025-05-10', 'Initial batch setup'),
(2, 2, 5, 13, 12, 1, 'completed', '2025-08-14', '2025-08-14', 'Production run'),
(3, 3, 2, 3, 3, 0, 'completed', '2025-11-20', '2025-11-20', 'Assembly line'),
(4, 5, 6, 52, 50, 2, 'completed', '2026-02-15', '2026-02-15', 'Replaced older stock seals'),
(5, 1, 1, 8, 8, 0, 'completed', '2026-04-05', '2026-04-05', 'Opening FY batch'),
(6, 2, 3, 4, 4, 0, 'completed', '2026-04-18', '2026-04-18', 'Standard install'),
(7, 3, 4, 2, 2, 0, 'completed', '2026-05-10', '2026-05-10', 'Custom machine assembly'),
(8, 1, 6, 105, 100, 5, 'completed', '2026-06-12', '2026-06-12', 'Bulk maintenance work'),
(9, 4, 7, 1, 1, 0, 'completed', '2026-06-20', '2026-06-20', 'High precision calibration unit'),
(10, 5, 5, 16, 15, 1, 'completed', '2026-06-22', '2026-06-22', 'Line expansion logs'),
(11, 2, 1, 500, 0, 0, 'pending', '2026-06-22', NULL, 'Pending buffing work');