CREATE TABLE IF NOT EXISTS uploads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  month_id INT NOT NULL,
  tipo CHAR(1) NOT NULL DEFAULT 'S',
  filename VARCHAR(255) NOT NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS months (
  id INT AUTO_INCREMENT PRIMARY KEY,
  year SMALLINT NOT NULL,
  month TINYINT NOT NULL,
  label VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_year_month (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS base_lancamentos (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  upload_id INT NOT NULL,
  month_id INT NOT NULL,
  tipo CHAR(1) NOT NULL DEFAULT 'S',
  cia TINYINT NOT NULL,
  data_emissao DATE NOT NULL,
  nota VARCHAR(20) NULL,
  cfop VARCHAR(10) NOT NULL,
  valor DECIMAL(15,2) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_data (data_emissao),
  INDEX idx_cia (cia),
  INDEX idx_cfop (cfop),
  INDEX idx_nota (nota),
  INDEX idx_month (month_id),
  INDEX idx_tipo (tipo),
  CONSTRAINT fk_base_upload FOREIGN KEY (upload_id) REFERENCES uploads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE uploads
  ADD INDEX idx_upload_month (month_id),
  ADD CONSTRAINT fk_upload_month FOREIGN KEY (month_id) REFERENCES months(id) ON DELETE CASCADE;

CREATE TABLE IF NOT EXISTS settings (
  id TINYINT PRIMARY KEY,
  use_cfop_filter TINYINT NOT NULL DEFAULT 0,
  meta_total DECIMAL(15,2) NULL,
  meta_months TINYINT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO settings (id, use_cfop_filter, meta_total, meta_months) VALUES (1, 0, NULL, NULL);

CREATE TABLE IF NOT EXISTS cfop_allowed (
  cfop VARCHAR(10) PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Usuários / permissões
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NULL,
  is_admin TINYINT NOT NULL DEFAULT 0,
  can_resumo TINYINT NOT NULL DEFAULT 1,
  can_upload TINYINT NOT NULL DEFAULT 0,
  can_base   TINYINT NOT NULL DEFAULT 0,
  can_config TINYINT NOT NULL DEFAULT 0,
  must_change_password TINYINT NOT NULL DEFAULT 1,
  active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin padrão (admin / 1234) é criado automaticamente ao abrir o sistema.