-- ============================================================
-- ARMAZÉM DE ADUBO - AGENDAMENTO (DB SCHEMA)
-- ============================================================

CREATE DATABASE IF NOT EXISTS agrofert_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE agrofert_db;

-- 1. PERFIS E PERMISSÕES
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255)
) ENGINE=InnoDB;

INSERT INTO roles (name, description) VALUES 
('ADMINISTRADOR', 'Acesso total ao sistema'),
('OPERADOR', 'Acesso operacional e agendamentos'),
('CLIENTE', 'Acesso restrito à área do cliente');

-- 2. USUÁRIOS
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    status ENUM('ATIVO', 'BLOQUEADO') DEFAULT 'ATIVO',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- 3. CLIENTES
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT, -- Relacionamento com o usuário do cliente
    code VARCHAR(20) UNIQUE,
    company_name VARCHAR(200) NOT NULL,
    trade_name VARCHAR(200),
    document VARCHAR(30) NOT NULL UNIQUE, -- CNPJ/CPF
    state_registration VARCHAR(30),
    email VARCHAR(150),
    phone VARCHAR(30),
    whatsapp VARCHAR(30),
    zip_code VARCHAR(15),
    address VARCHAR(255),
    number VARCHAR(20),
    complement VARCHAR(100),
    neighborhood VARCHAR(100),
    city VARCHAR(100),
    state CHAR(2),
    manager_name VARCHAR(120),
    manager_email VARCHAR(150),
    manager_phone VARCHAR(30),
    notes TEXT,
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 4. PRODUTOS
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(20) UNIQUE,
    name VARCHAR(150) NOT NULL,
    description TEXT,
    category VARCHAR(100),
    brand VARCHAR(100),
    unit ENUM('TON', 'KG', 'SC', 'BIG BAG') DEFAULT 'TON',
    type VARCHAR(100), -- Ex: Granulado, Fertilizante
    weight DECIMAL(10,3),
    stock_current DECIMAL(14,3) DEFAULT 0,
    stock_min DECIMAL(14,3) DEFAULT 0,
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 5. TRANSPORTADORAS
CREATE TABLE IF NOT EXISTS carriers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    trade_name VARCHAR(200),
    document VARCHAR(30) UNIQUE,
    state_registration VARCHAR(30),
    phone VARCHAR(30),
    whatsapp VARCHAR(30),
    email VARCHAR(150),
    address VARCHAR(255),
    city VARCHAR(100),
    state CHAR(2),
    manager_name VARCHAR(120),
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 6. MOTORISTAS
CREATE TABLE IF NOT EXISTS drivers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    carrier_id INT NOT NULL,
    name VARCHAR(120) NOT NULL,
    cpf VARCHAR(20) UNIQUE NOT NULL,
    cnh VARCHAR(20) UNIQUE,
    cnh_category VARCHAR(10),
    cnh_expiration DATE,
    phone VARCHAR(30),
    whatsapp VARCHAR(30),
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (carrier_id) REFERENCES carriers(id)
) ENGINE=InnoDB;

-- 7. VEÍCULOS
CREATE TABLE IF NOT EXISTS vehicles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    carrier_id INT NOT NULL,
    plate VARCHAR(15) UNIQUE NOT NULL,
    plate_trailer VARCHAR(15),
    brand VARCHAR(50),
    model VARCHAR(100),
    type ENUM('RODOTREM', 'BITREM', 'CARRETA', 'TRUCK', 'TOCO', 'OUTROS') DEFAULT 'CARRETA',
    capacity DECIMAL(10,3),
    body_type VARCHAR(100),
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (carrier_id) REFERENCES carriers(id)
) ENGINE=InnoDB;

-- 8. ORDENS DE VENDA
CREATE TABLE IF NOT EXISTS sales_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    product_id INT NOT NULL,
    total_volume DECIMAL(14,3) NOT NULL,
    scheduled_volume DECIMAL(14,3) DEFAULT 0,
    loaded_volume DECIMAL(14,3) DEFAULT 0,
    status ENUM('ABERTA', 'PARCIAL', 'CONCLUIDA', 'CANCELADA') DEFAULT 'ABERTA',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

-- 9. CONFIGURAÇÃO DE HORÁRIOS
CREATE TABLE IF NOT EXISTS schedules_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time_slot TIME NOT NULL UNIQUE,
    max_vehicles INT DEFAULT 1,
    max_volume DECIMAL(14,3) DEFAULT 0,
    days_week VARCHAR(20) DEFAULT '1,2,3,4,5', -- 0=Dom, 1=Seg...
    status ENUM('ATIVO', 'INATIVO') DEFAULT 'ATIVO'
) ENGINE=InnoDB;

-- 10. AGENDAMENTOS
CREATE TABLE IF NOT EXISTS appointments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sales_order_id INT NOT NULL,
    client_id INT NOT NULL,
    product_id INT NOT NULL,
    carrier_id INT,
    driver_id INT,
    vehicle_id INT,
    appointment_date DATE NOT NULL,
    appointment_time TIME NOT NULL,
    volume DECIMAL(14,3) NOT NULL,
    manager_name VARCHAR(120),
    incoterm VARCHAR(20),
    driver_contact VARCHAR(30),
    driver_cpf VARCHAR(20),
    plate VARCHAR(15),
    trailer_1 VARCHAR(15),
    trailer_2 VARCHAR(15),
    trailer_3 VARCHAR(15),
    vehicle_type VARCHAR(50),
    destination_city VARCHAR(100),
    destination_uf CHAR(2),
    notes TEXT,
    status ENUM('SOLICITADO', 'PENDENTE', 'CONFIRMADO', 'EM ESPERA', 'EM CARREGAMENTO', 'CONCLUIDO', 'CANCELADO', 'REAGENDADO', 'NAO COMPARECEU') DEFAULT 'SOLICITADO',
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sales_order_id) REFERENCES sales_orders(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (carrier_id) REFERENCES carriers(id),
    FOREIGN KEY (driver_id) REFERENCES drivers(id),
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

-- 11. HISTÓRICO DE STATUS
CREATE TABLE IF NOT EXISTS appointment_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_id INT NOT NULL,
    user_id INT NOT NULL,
    previous_status VARCHAR(50),
    new_status VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- 12. DOCUMENTOS
CREATE TABLE IF NOT EXISTS appointment_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    appointment_id INT NOT NULL,
    user_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (appointment_id) REFERENCES appointments(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- 13. AUDITORIA
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    module VARCHAR(50) NOT NULL,
    record_id INT,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- 14. NOTIFICAÇÕES
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(200),
    message TEXT,
    is_read TINYINT(1) DEFAULT 0,
    type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

-- DADOS INICIAIS
INSERT INTO users (role_id, name, email, password) VALUES 
(1, 'Administrador Master', 'admin@agrofert.com', '$2y$10$e0NRs3s0Cg5B7GjOaG0S0eYy1nJq0h5v4hDkq3sHqz9Zx3Jj6i0Zi');

-- Horários padrão
INSERT INTO schedules_config (time_slot) VALUES 
('07:00:00'), ('08:00:00'), ('09:00:00'), ('10:00:00'), ('11:00:00'), 
('13:00:00'), ('14:00:00'), ('15:00:00'), ('16:00:00'), ('17:00:00');