CREATE DATABASE IF NOT EXISTS evdb;
USE evdb;
/* Client Table */
CREATE TABLE IF NOT EXISTS clients (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT 'client_id',
    name VARCHAR(250) NOT NULL,
    lastname VARCHAR(250),
    phone VARCHAR(50),
    email VARCHAR(200),
    address VARCHAR(500),
    notes TEXT,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_search_fields` (name(100), lastname(100), phone, email, address(200))
);

/* Jobs Table */
CREATE TABLE IF NOT EXISTS jobs (
    id INT PRIMARY KEY AUTO_INCREMENT NOT NULL COMMENT 'job_id',
    details TEXT NOT NULL,
    status INT COMMENT '0 = No iniciado | 1 = En proceso | 2 = Finalizado | 3 = Pagado',
    client_id INT NOT NULL,
    quantity FLOAT,
    price FLOAT,
    total FLOAT,
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_jobs_clients FOREIGN KEY (client_id) REFERENCES clients(id)
);

INSERT INTO clients (name, lastname, phone, email, address, notes)
VALUES
('Juan', 'Pérez', '+525512345678', 'juan.perez@example.com', 'Av. Reforma 123, CDMX', 'Cliente frecuente, paga en efectivo'),
('María', 'López', '+528198765432', 'maria.lopez@example.com', 'Calle Morelos 456, Monterrey', 'Requiere factura'),
('Carlos', 'García', '+523355556666', 'carlos.garcia@example.com', 'Av. Patria 789, Guadalajara', 'Prefiere contacto por correo');

INSERT INTO jobs (details, status, client_id, quantity, price, total)
VALUES
('Reparación de sofá de 3 plazas', 1, 1, 1, 3500.00, 3500.00),
('Tapizado de sillas comedor (6 piezas)', 0, 2, 6, 800.00, 4800.00),
('Restauración de sillón reclinable', 2, 3, 1, 2500.00, 2500.00),
('Limpieza profunda de sala', 3, 1, 1, 1500.00, 1500.00);