-- Script SQL para importar as tabelas
CREATE DATABASE IF NOT EXISTS ci_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ci_system;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(150) NOT NULL UNIQUE,
  username VARCHAR(100),
  password VARCHAR(255) NOT NULL,
  role ENUM('admin','manager','user') DEFAULT 'user',
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome_razao VARCHAR(200) NOT NULL,
  cpf_cnpj VARCHAR(30),
  email VARCHAR(150),
  telefone VARCHAR(50),
  endereco_rua VARCHAR(255),
  endereco_numero VARCHAR(50),
  endereco_complemento VARCHAR(150),
  bairro VARCHAR(150),
  cidade VARCHAR(150),
  estado VARCHAR(50),
  cep VARCHAR(20),
  logo_path VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE drivers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(200) NOT NULL,
  cpf VARCHAR(30),
  email VARCHAR(150),
  telefone VARCHAR(50),
  endereco_rua VARCHAR(255),
  endereco_numero VARCHAR(50),
  endereco_complemento VARCHAR(150),
  bairro VARCHAR(150),
  cidade VARCHAR(150),
  estado VARCHAR(50),
  cep VARCHAR(20),
  carro_marca VARCHAR(100),
  carro_modelo VARCHAR(100),
  carro_ano SMALLINT,
  placa VARCHAR(20),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  owner_type ENUM('client','driver','company','other') NOT NULL,
  owner_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_size INT,
  mime VARCHAR(100),
  uploaded_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_number VARCHAR(50) NOT NULL UNIQUE,
  company_id INT DEFAULT 1,
  client_id INT NOT NULL,
  driver_id INT,
  date_issue DATE,
  due_date DATE,
  status ENUM('open','paid','cancelled') DEFAULT 'open',
  subtotal DECIMAL(12,2) DEFAULT 0,
  discount DECIMAL(12,2) DEFAULT 0,
  discount_type ENUM('fixed','percent') DEFAULT 'fixed',
  additional DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0,
  recurring TINYINT(1) DEFAULT 0,
  recurring_period ENUM('monthly','weekly','yearly'),
  recurring_next_date DATE,
  notes TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  title VARCHAR(255),
  description TEXT,
  qty DECIMAL(12,2) DEFAULT 1,
  unit_type VARCHAR(50) DEFAULT 'un',
  unit_price DECIMAL(12,2) DEFAULT 0,
  total DECIMAL(12,2) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  paid_at DATETIME,
  amount DECIMAL(12,2),
  method VARCHAR(100),
  proof_file_id INT,
  created_by INT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  company_name VARCHAR(200),
  cnpj VARCHAR(50),
  email VARCHAR(150),
  telefone VARCHAR(50),
  endereco_rua VARCHAR(255),
  endereco_numero VARCHAR(50),
  endereco_complemento VARCHAR(150),
  bairro VARCHAR(150),
  cidade VARCHAR(150),
  estado VARCHAR(50),
  cep VARCHAR(20),
  logo_path VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert admin user (senha md5: admin123)
INSERT INTO users (name,email,username,password,role,active) VALUES ('Administrador','admin@admin.com','admin', MD5('admin123'),'admin',1);