CREATE DATABASE IF NOT EXISTS macinv;
USE macinv;

CREATE TABLE `factories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `is_super_admin` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `role_permissions` (
  `role_id` int(11) NOT NULL,
  `permission_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  CONSTRAINT `rp_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rp_perm` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role_id` int(11) NOT NULL,
  `status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `user_factories` (
  `user_id` int(11) NOT NULL,
  `factory_id` int(11) NOT NULL,
  PRIMARY KEY (`user_id`,`factory_id`),
  CONSTRAINT `uf_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `uf_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `factory_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `map_image_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `loc_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `machines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `factory_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `model` varchar(100) DEFAULT NULL,
  `installed_date` date DEFAULT NULL,
  `status` enum('Active','Under Maintenance','Broken') NOT NULL DEFAULT 'Active',
  `location_id` int(11) DEFAULT NULL,
  `map_x` float DEFAULT NULL,
  `map_y` float DEFAULT NULL,
  `qr_code_uid` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `qr_code_uid` (`qr_code_uid`),
  KEY `location_id` (`location_id`),
  CONSTRAINT `mac_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `mac_loc` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `maintenance_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `factory_id` int(11) NOT NULL,
  `machine_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `issue_description` text NOT NULL,
  `action_taken` text,
  `parts_replaced` text,
  `photo_url` varchar(255) DEFAULT NULL,
  `photo_url_2` varchar(255) DEFAULT NULL,
  `maintained_by` varchar(100) DEFAULT NULL,
  `maintenance_date` date DEFAULT NULL,
  `about_maintenance` varchar(255) DEFAULT NULL,
  `maintenance_cost` decimal(10,2) DEFAULT NULL,
  `disposed` tinyint(1) NOT NULL DEFAULT 0,
  `sent_to_main_store` tinyint(1) NOT NULL DEFAULT 0,
  `sold` tinyint(1) NOT NULL DEFAULT 0,
  `sold_to` varchar(100) DEFAULT NULL,
  `sold_amount` decimal(10,2) DEFAULT NULL,
  `downtime_days` int(11) NOT NULL DEFAULT 0,
  `downtime_hours` int(11) NOT NULL DEFAULT 0,
  `removed_items_details` text DEFAULT NULL,
  `store_receipt_status` enum('N/A','Pending','Received') NOT NULL DEFAULT 'N/A',
  `store_receipt_remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `ml_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ml_machine` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ml_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- INITIAL DATA --
INSERT INTO `factories` (`name`) VALUES ('Main Headquarter Factory');

INSERT INTO `permissions` (`name`, `description`) VALUES 
('web_login', 'Allow login to web dashboard'),
('app_login', 'Allow login to mobile app'),
('manage_users', 'Can create and edit users & roles'),
('manage_factories', 'Can create and edit factories'),
('manage_machines', 'Can add and edit machines and locations'),
('add_maintenance', 'Can add maintenance logs'),
('manage_store', 'Can manage stores receiving');

INSERT INTO `roles` (`name`, `is_super_admin`) VALUES ('Super Admin', 1);
INSERT INTO `roles` (`name`, `is_super_admin`) VALUES ('Technician', 0);
INSERT INTO `roles` (`name`, `is_super_admin`) VALUES ('Stores Manager', 0);

-- Assign all permissions to Super Admin is not strictly needed if is_super_admin=1 overrides it, but let's be explicit
INSERT INTO `role_permissions` (`role_id`, `permission_id`) 
SELECT 1, id FROM permissions;

-- Assign technician permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`) 
SELECT 2, id FROM permissions WHERE name IN ('app_login', 'add_maintenance');

-- Assign stores manager permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`) 
SELECT 3, id FROM permissions WHERE name IN ('app_login', 'manage_store');

-- Insert default admin (password: admin123)
INSERT INTO `users` (`username`, `email`, `password_hash`, `role_id`, `status`) VALUES
('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1, 'Active');

INSERT INTO `user_factories` (`user_id`, `factory_id`) VALUES (1, 1);