CREATE DATABASE IF NOT EXISTS device_manager;
USE device_manager;

CREATE TABLE IF NOT EXISTS devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cui VARCHAR(50) NOT NULL COMMENT 'CUI',
    alias VARCHAR(100) COMMENT 'Alias',
    office_name VARCHAR(150) COMMENT 'Nombre Oficina',
    region VARCHAR(100) COMMENT 'Region',
    commune VARCHAR(100) COMMENT 'Comuna',
    address VARCHAR(255) COMMENT 'Dirección',
    brand VARCHAR(100) COMMENT 'Marca',
    ip_address VARCHAR(45) COMMENT 'IP',
    mac_address VARCHAR(17) COMMENT 'MAC',
    observations TEXT COMMENT 'Observaciones',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS replacements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id INT NOT NULL,
    replaced_parts VARCHAR(255) COMMENT 'Cambio Repuestos',
    month VARCHAR(20) COMMENT 'Mes',
    year INT COMMENT 'Año',
    replacement_date DATE COMMENT 'Fecha Cambio Repuestos',
    observations TEXT COMMENT 'Observaciones',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;