-- Database Schema for Velvet Rose
-- Compatible with MySQL/PostgreSQL

-- 1. Suppliers (Proveedores)
CREATE TABLE Suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    contact_info VARCHAR(255),
    category VARCHAR(50) -- e.g., 'Textiles', 'Insumos'
);

-- 2. Materials (Telas e Insumos)
CREATE TABLE Materials (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'Fabric', 'Accessory'
    unit VARCHAR(20) NOT NULL, -- 'meter', 'unit', 'pack'
    cost_per_unit DECIMAL(10, 2) NOT NULL,
    supplier_id INT,
    FOREIGN KEY (supplier_id) REFERENCES Suppliers(id)
);

-- 3. Product Models (Categorías de prendas con base de costos)
CREATE TABLE ProductModels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL, -- 'Blusa', 'Vestido', etc.
    description TEXT,
    base_labor_hours DECIMAL(4, 2) DEFAULT 1.0, -- Default time to produce in hours
    image_url VARCHAR(255)
);

-- 4. Products (Variantes específicas)
CREATE TABLE Products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    model_id INT,
    name VARCHAR(150) NOT NULL, -- e.g., 'Blusa Manga Corta Lino'
    sku VARCHAR(50) UNIQUE,
    current_price DECIMAL(10, 2),
    stock_quantity INT DEFAULT 0,
    image_data LONGTEXT, -- Base64 string or URL for product image
    FOREIGN KEY (model_id) REFERENCES ProductModels(id)
);

-- 5. Bill of Materials (Receta de Costos)
CREATE TABLE ProductBOM (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    material_id INT,
    quantity_required DECIMAL(6, 2) NOT NULL, -- e.g., 1.5 meters of fabric
    FOREIGN KEY (product_id) REFERENCES Products(id),
    FOREIGN KEY (material_id) REFERENCES Materials(id)
);

-- 6. Production Batches (Lotes de Producción)
CREATE TABLE ProductionBatches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    batch_size INT NOT NULL,
    start_date DATE,
    estimated_delivery_date DATE,
    status VARCHAR(50) DEFAULT 'Planned', -- 'Planned', 'In Progress', 'Completed'
    total_cost DECIMAL(12, 2),
    FOREIGN KEY (product_id) REFERENCES Products(id)
);

-- Initial Data Seeding (Datos de Ejemplo)
INSERT INTO Suppliers (name, category) VALUES 
('Textiles Andino', 'Fabrics'),
('Hilos Dorado', 'Accessories');

INSERT INTO Materials (name, type, unit, cost_per_unit, supplier_id) VALUES 
('Algodón Premium', 'Fabric', 'meter', 5.00, 1),
('Lino Puro', 'Fabric', 'meter', 8.50, 1),
('Denim Stretch', 'Fabric', 'meter', 7.00, 1),
('Grafeno Tech', 'Fabric', 'meter', 15.00, 1),
('Hilo Poliéster', 'Accessory', 'roll', 2.00, 2),
('Botones Nacar', 'Accessory', 'unit', 0.10, 2),
('Cierre Invisible', 'Accessory', 'unit', 0.50, 2);

INSERT INTO ProductModels (name, base_labor_hours) VALUES 
('Blusa', 1.5),
('Top', 0.8),
('Falda', 1.2),
('Pantalón', 2.0),
('Vestido', 2.5);
