USE appventas-dev;

DROP PROCEDURE IF EXISTS safe_migration;

DELIMITER $$

CREATE PROCEDURE safe_migration()
BEGIN

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;

DELETE FROM transactions WHERE sale_detail_id IS NOT NULL;

ALTER TABLE transactions DROP FOREIGN KEY fk_transactions_sale_detail;
ALTER TABLE transactions DROP FOREIGN KEY fk_transactions_kardex_type;

DROP INDEX idx_transactions_sale_detail ON transactions;
DROP INDEX idx_transactions_kardex_type ON transactions;

ALTER TABLE transactions DROP COLUMN sale_detail_id;
ALTER TABLE transactions DROP COLUMN kardex_type_id;

ALTER TABLE transactions MODIFY transaction_type_id BIGINT(20) UNSIGNED NULL;

END$$

DELIMITER ;

CALL safe_migration();


-- Eliminar tablas en orden inverso de dependencias (hijas primero, padres después)

-- =========================
-- TABLAS PRINCIPALES (en orden de dependencias)
-- =========================

CREATE TABLE `sa_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `parent_category_id` int(11) DEFAULT NULL,
  `offer_code` varchar(255) DEFAULT NULL,
  `state` varchar(2) NOT NULL DEFAULT 'A',
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_category_id` (`parent_category_id`),
  FOREIGN KEY (`parent_category_id`) REFERENCES `sa_category` (`id`)
);

CREATE TABLE `sa_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `state` varchar(1) NOT NULL DEFAULT 'A',
  `offer_code` varchar(255) DEFAULT NULL,
  `purchase_price` decimal(12,2) DEFAULT NULL COMMENT 'Precio de compra base del producto',
  `sale_price` decimal(12,2) DEFAULT NULL COMMENT 'Precio de venta base del producto',
  `category_id` int(11) DEFAULT NULL COMMENT 'Categoría del producto',
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `category_id` (`category_id`),
  FOREIGN KEY (`category_id`) REFERENCES `sa_category` (`id`)
);

CREATE TABLE `sa_payment_method` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `color` varchar(20) DEFAULT NULL,
  `icon_name` varchar(100) DEFAULT NULL,
  `is_credit` int(1) NOT NULL DEFAULT 0,
  `is_default` int(1) NOT NULL DEFAULT 0,
  `org_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
);

CREATE TABLE `sa_kardex_entry_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(5) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `created_by_id` bigint(20) UNSIGNED NOT NULL,
  `is_default` int(1) NOT NULL,
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by_id` (`created_by_id`),
  FOREIGN KEY (`created_by_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `sa_provider_sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `balance` decimal(10,2) DEFAULT 0.00,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `org_id` int(11) NOT NULL,
  `state` char(1) NOT NULL DEFAULT 'A' COMMENT 'A=Activo, B=Inactivo, E=Eliminado',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
);



-- =========================
-- TABLAS DEPENDIENTES
-- =========================

CREATE TABLE `sa_product_slot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `initial_quantity` int(11) NOT NULL,
  `stock` decimal(12,2) NOT NULL,
  `priority` int(11) DEFAULT NULL,
  `min_stock` int(11) DEFAULT NULL,
  `expiration_date` date DEFAULT NULL,
  `unit_price` decimal(12,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  FOREIGN KEY (`product_id`) REFERENCES `sa_product` (`id`)
);


CREATE TABLE `sa_sale` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `kardex_entry_type_id` int(11) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `subtotal` decimal(10,2) NOT NULL,
  `total` decimal(10,2) NOT NULL,
  `discount_percentage` int(11) DEFAULT NULL,
  `discount_amount` decimal(10,2) DEFAULT NULL,
  `discount_comment` text DEFAULT NULL,
  `state` varchar(2) NOT NULL DEFAULT 'A',
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `kardex_entry_type_id` (`kardex_entry_type_id`),
  FOREIGN KEY (`kardex_entry_type_id`) REFERENCES `sa_kardex_entry_type` (`id`)
);

CREATE TABLE `sa_sale_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `client_id` bigint(20) UNSIGNED NOT NULL,
  `type` varchar(100) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `service_id` int(11) DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `payment_method_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`),
  KEY `category_id` (`category_id`),
  KEY `client_id` (`client_id`),
  KEY `payment_method_id` (`payment_method_id`),
  FOREIGN KEY (`sale_id`) REFERENCES `sa_sale` (`id`),
  FOREIGN KEY (`product_id`) REFERENCES `sa_product` (`id`),
  FOREIGN KEY (`category_id`) REFERENCES `sa_category` (`id`),
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  FOREIGN KEY (`payment_method_id`) REFERENCES `sa_payment_method` (`id`)
);

CREATE TABLE `sa_purchase` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `movement_date` timestamp NOT NULL DEFAULT current_timestamp(),  
  `kardex_entry_type_id` int(11) NOT NULL,
  `comment` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `kardex_entry_type_id` (`kardex_entry_type_id`),
  FOREIGN KEY (`kardex_entry_type_id`) REFERENCES `sa_kardex_entry_type` (`id`)
);

CREATE TABLE `sa_kardex_entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state` varchar(2) NOT NULL DEFAULT 'A' COMMENT 'Estado A: Activo, B: Inactivo',
  `product_id` int(11) NOT NULL,
  `sale_detail_id` int(11) NULL,
  `purchase_id` int(11) DEFAULT NULL,
  `kardex_entry_type_id` int(11) NOT NULL,
  `previous_quantity` int(11) DEFAULT NULL,
  `quantity` int(11) NOT NULL,
  `balance` int(11) NOT NULL,
  `unit_price` decimal(12,2) DEFAULT NULL,
  `total_price` decimal(12,2) DEFAULT NULL,
  `client_id` bigint(20) UNSIGNED NULL,  
  `provider_id` int(20) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `color`	varchar(20) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `sale_detail_id` (`sale_detail_id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `client_id` (`client_id`),
  KEY `kardex_entry_type_id` (`kardex_entry_type_id`),  
  KEY `provider_id` (`provider_id`),
  FOREIGN KEY (`product_id`) REFERENCES `sa_product` (`id`),
  FOREIGN KEY (`sale_detail_id`) REFERENCES `sa_sale_detail` (`id`),
  FOREIGN KEY (`purchase_id`) REFERENCES `sa_purchase` (`id`),
  FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`),
  FOREIGN KEY (`kardex_entry_type_id`) REFERENCES `sa_kardex_entry_type` (`id`),  
  FOREIGN KEY (`provider_id`) REFERENCES `sa_provider_sale` (`id`)
);

CREATE TABLE `sa_filter_report_param` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(100) NOT NULL,
  `chart_type` enum('bar','line','scatter','bubble','pie','doughnut','polarArea','radar') NOT NULL,
  `group_by` enum('location','employee','client','shift','paymentMethod') NOT NULL,
  `filter_config` text NOT NULL,
  `show_legend` tinyint(1) NOT NULL DEFAULT 1,
  `show_data_labels` tinyint(1) NOT NULL DEFAULT 1,
  `org_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `sa_provider_payment` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `provider_sale_id` int(11) NOT NULL COMMENT 'Código del proveedor',
  `paymentMethod` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` decimal(14,2) NOT NULL,
  `state` varchar(2) NOT NULL DEFAULT 'A' COMMENT 'Estado A: Activo, B: Inactivo',
  `color` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_provider_sale_id` (`provider_sale_id`),
  KEY `idx_provider_payment_date` (`date`),
  FOREIGN KEY (`provider_sale_id`) REFERENCES `sa_provider_sale` (`id`)
);

-- Migration: Create daily_closing_head and daily_closing tables
-- Date: 2026-03-28

-- Create daily_closing_head table
CREATE TABLE IF NOT EXISTS `sa_daily_closing_head` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20)  unsigned NOT NULL COMMENT 'ID del usuario',
  `date` date NOT NULL COMMENT 'Fecha del cierre diario (YYYY-MM-DD)',
  PRIMARY KEY (`id`),
  KEY `idx_daily_closing_head_user_date` (`user_id`, `date`),
  CONSTRAINT `fk_daily_closing_head_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Cabecera de cierres diarios';

-- Create daily_closing table
CREATE TABLE IF NOT EXISTS `sa_daily_closing` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `daily_closing_head_id` bigint unsigned NOT NULL COMMENT 'ID de la cabecera del cierre diario',
  `description` varchar(255) NOT NULL COMMENT 'Descripción del gasto',
  `amount` decimal(10,2) NOT NULL COMMENT 'Monto del gasto',
  `category` varchar(100) NOT NULL COMMENT 'Categoría del gasto',
  `state` char(1) NOT NULL DEFAULT 'A' COMMENT 'Estado del registro: A=Activo, I=Inactivo',
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_daily_closing_head` (`daily_closing_head_id`),
  KEY `idx_daily_closing_category` (`category`),
  KEY `idx_daily_closing_state` (`state`),
  CONSTRAINT `fk_daily_closing_head` FOREIGN KEY (`daily_closing_head_id`) REFERENCES `sa_daily_closing_head` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Detalles de gastos del cierre diario';

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS `idx_daily_closing_head_user_date` ON `sa_daily_closing_head` (`user_id`, `date`);
CREATE INDEX IF NOT EXISTS `idx_daily_closing_amount` ON `sa_daily_closing` (`amount`);



-- Adicionar columnas a transactions
ALTER TABLE transactions ADD sale_detail_id INT(11) NULL AFTER delivery_id;
ALTER TABLE transactions ADD kardex_type_id INT(11) NULL AFTER transaction_type_id;
ALTER TABLE transactions ADD CONSTRAINT fk_transactions_sale_detail FOREIGN KEY (sale_detail_id) REFERENCES sa_sale_detail(id);
ALTER TABLE transactions ADD CONSTRAINT fk_transactions_kardex_type FOREIGN KEY (kardex_type_id) REFERENCES sa_kardex_entry_type(id);
CREATE INDEX idx_transactions_sale_detail ON transactions(sale_detail_id);
CREATE INDEX idx_transactions_kardex_type ON transactions(kardex_type_id);


-- insert into
INSERT INTO sa_category (name, description, org_id) VALUES ('Plátanos', 'Categoría general de precios', 6);

INSERT INTO sa_product (name, description, purchase_price, sale_price, category_id, org_id) VALUES 
('BUENAS VERDES', 'Producto calidad BUENAS VERDES', 85000, 95000, 1, 6), 
('BUENAS AMARILLAS', 'Producto calidad BUENAS AMARILLAS', 85000, 95000, 1, 6), 
('SEGUNDAS VERDES', 'Producto calidad SEGUNDAS VERDES', 58000, 68000, 1, 6), 
('BUENAS MADURAS', 'Producto calidad BUENAS MADURAS', 55000, 65000, 1, 6), 
('SEGUNDAS AMARILLAS', 'Producto calidad SEGUNDAS AMARILLAS', 50000, 60000, 1, 6), 
('PICAS VERDES', 'Producto calidad PICAS VERDES', 38000, 48000, 1, 6), 
('NATURALES', 'Producto calidad NATURALES', 60000, 70000, 1, 6), 
('BUENAS REPICHAS', 'Producto calidad BUENAS REPICHAS', 12750, 17750, 1, 6), 
('PICAS MADURAS', 'Producto calidad PICAS MADURAS', 20000, 25000, 1, 6), 
('RAJADOS', 'Producto calidad RAJADOS', 0, 0, 1, 6), 
('BUENAS VER/AM', 'Producto calidad BUENAS VER/AM', 0, 0, 1, 6);

INSERT INTO sa_provider_sale 
(name, balance, phone, email, address, org_id, state)
VALUES
('Proveedor La Huerta', 0.00, '3001230001', 'lahuerta@gmail.com', 'Finca La Huerta, Zona Rural', 6, 'A'),
('Distribuciones El Campo', 0.00, '3001230002', 'elcampo@gmail.com', 'Carrera 10 #20-30', 6, 'A'),
('AgroVerde S.A.S', 0.00, '3001230003', 'agroverde@gmail.com', 'Vereda San José', 6, 'A'),
('Productos Campesinos', 0.00, '3001230004', 'campesinos@gmail.com', 'Km 5 Vía Principal', 6, 'A'),
('Finca El Progreso', 0.00, '3001230005', 'elprogreso@gmail.com', 'Zona Rural Norte', 6, 'A'),
('Mercados Naturales', 0.00, '3001230006', 'naturales@gmail.com', 'Calle 45 #12-50', 6, 'A'),
('AgroColombia', 0.00, '3001230007', 'agrocolombia@gmail.com', 'Parque Industrial', 6, 'A'),
('Campo y Tierra', 0.00, '3001230008', 'campoytierra@gmail.com', 'Vereda El Rosario', 6, 'A'),
('Finca Los Andes', 0.00, '3001230009', 'losandes@gmail.com', 'Zona Rural Sur', 6, 'A'),
('Distribuciones San Pedro', 0.00, '3001230010', 'sanpedro@gmail.com', 'Av Principal #100-20', 6, 'A');

-- Insertar tipos de kardex básicos
INSERT INTO sa_kardex_entry_type (name, type, description, created_by_id, is_default, org_id) VALUES
('Compra a Proveedor', 'IN', 'Entrada de inventario por compra a proveedor', 1, 1, 6),
('Venta a Cliente', 'OUT', 'Salida de inventario por venta a cliente', 1, 1, 6),
('Ajuste Positivo', 'IN', 'Ajuste de inventario - incremento', 1, 0, 6),
('Ajuste Negativo', 'OUT', 'Ajuste de inventario - decremento', 1, 0, 6),
('Devolución Cliente', 'IN', 'Entrada por devolución de cliente', 1, 0, 6),
('Devolución a Proveedor', 'OUT', 'Salida por devolución a proveedor', 1, 0, 6),
('Merma', 'OUT', 'Salida por merma o pérdida', 1, 0, 6),
('Traslado Entrada', 'IN', 'Entrada por traslado entre bodegas', 1, 0, 6),
('Traslado Salida', 'OUT', 'Salida por traslado entre bodegas', 1, 0, 6);

