-- Extensiones para el sistema de ventas.
-- Ejecutar una sola vez después de importar el dump original ansangue_encanto(2).sql.
-- Compatible con MariaDB 10.11+.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `cash_register_id` int(11) DEFAULT NULL,
  `expense_date` datetime NOT NULL DEFAULT current_timestamp(),
  `category` varchar(80) NOT NULL DEFAULT 'Otros',
  `description` text NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `payment_method` enum('cash','transfer','card','other') NOT NULL DEFAULT 'cash',
  `status` enum('active','cancelled') 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`),
  KEY `idx_expenses_date_status` (`expense_date`, `status`),
  KEY `idx_expenses_user` (`user_id`),
  KEY `idx_expenses_cash_register` (`cash_register_id`),
  CONSTRAINT `expenses_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `expenses_cash_fk` FOREIGN KEY (`cash_register_id`) REFERENCES `cash_register` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `sale_details`
  ADD COLUMN IF NOT EXISTS `unit_cost` decimal(10,2) NOT NULL DEFAULT 0.00 AFTER `price`,
  ADD COLUMN IF NOT EXISTS `line_profit` decimal(12,2) NOT NULL DEFAULT 0.00 AFTER `subtotal`;

UPDATE `sale_details` sd
INNER JOIN `products` p ON p.id = sd.product_id
SET sd.unit_cost = IF(sd.unit_cost = 0, p.purchase_price, sd.unit_cost),
    sd.line_profit = IF(sd.line_profit = 0, (sd.subtotal - (p.purchase_price * sd.quantity)), sd.line_profit);

ALTER TABLE `sales`
  ADD COLUMN IF NOT EXISTS `cancelled_at` datetime DEFAULT NULL AFTER `updated_at`,
  ADD COLUMN IF NOT EXISTS `cancelled_by` int(11) DEFAULT NULL AFTER `cancelled_at`,
  ADD COLUMN IF NOT EXISTS `cancel_reason` text DEFAULT NULL AFTER `cancelled_by`;

ALTER TABLE `sales`
  ADD INDEX IF NOT EXISTS `idx_sales_status_created_at` (`status`, `created_at`),
  ADD INDEX IF NOT EXISTS `idx_sales_cash_register_id` (`cash_register_id`);

ALTER TABLE `sale_details`
  ADD INDEX IF NOT EXISTS `idx_sale_details_product_sale` (`product_id`, `sale_id`);

ALTER TABLE `cash_movements`
  ADD INDEX IF NOT EXISTS `idx_cash_movements_cash_date` (`cash_register_id`, `created_at`),
  ADD INDEX IF NOT EXISTS `idx_cash_movements_reference` (`reference_type`, `reference_id`);

ALTER TABLE `products`
  ADD INDEX IF NOT EXISTS `idx_products_status_category` (`status`, `category_id`);

INSERT IGNORE INTO `permissions` (`name`, `description`, `module`) VALUES
('expenses.view', 'Ver gastos', 'expenses'),
('expenses.create', 'Crear gastos', 'expenses'),
('expenses.cancel', 'Anular gastos', 'expenses'),
('settings.view', 'Ver ajustes', 'settings'),
('settings.permissions', 'Asignar permisos por rol', 'settings');

-- Administrador: todos los permisos existentes y nuevos.
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 1, id FROM `permissions`;

-- Vendedor: acceso operativo básico.
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 2, id FROM `permissions`
WHERE name IN ('dashboard.view','sales.create','sales.view','cash.open','cash.close','cash.view','products.view','categories.view','inventory.view');

-- Supervisor: ventas, caja, productos, categorías, gastos y reportes.
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 3, id FROM `permissions`
WHERE module IN ('dashboard','sales','cash','products','categories','expenses','reports','inventory');

-- Contador: reportes, gastos, ventas y caja de consulta.
INSERT IGNORE INTO `role_permissions` (`role_id`, `permission_id`)
SELECT 4, id FROM `permissions`
WHERE name IN ('dashboard.view','reports.view','expenses.view','expenses.create','sales.view','cash.view');