-- Banco e schema (password_hash)
CREATE DATABASE IF NOT EXISTS compras_solicitacoes
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE compras_solicitacoes;

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(160) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('requester','approver','admin') NOT NULL DEFAULT 'requester',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  requester_id INT NOT NULL,
  department_id INT NULL,
  needed_by DATE NULL,
  justification TEXT NULL,
  status ENUM('ENVIADA','APROVADA','REPROVADA','FINALIZADA','CANCELADA') NOT NULL DEFAULT 'ENVIADA',
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_at DATETIME NULL,
  finalized_at DATETIME NULL,
  final_obs VARCHAR(255) NULL,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE IF NOT EXISTS request_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  item_desc VARCHAR(255) NOT NULL,
  qty DECIMAL(12,3) NOT NULL DEFAULT 1,
  unit VARCHAR(20) NULL,
  unit_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  FOREIGN KEY (request_id) REFERENCES requests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS approvals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  approver_id INT NOT NULL,
  decision ENUM('APROVAR','REPROVAR') NOT NULL,
  comments VARCHAR(255) NULL,
  decided_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES requests(id),
  FOREIGN KEY (approver_id) REFERENCES users(id)
);

CREATE OR REPLACE VIEW v_request_totals AS
SELECT r.id AS request_id, COALESCE(SUM(ri.qty * ri.unit_price),0) AS calc_total
FROM requests r
LEFT JOIN request_items ri ON ri.request_id = r.id
GROUP BY r.id;

DELIMITER //
CREATE TRIGGER trg_items_ai AFTER INSERT ON request_items
FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = NEW.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = NEW.request_id;
END//
CREATE TRIGGER trg_items_au AFTER UPDATE ON request_items
FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = NEW.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = NEW.request_id;
END//
CREATE TRIGGER trg_items_ad AFTER DELETE ON request_items
FOR EACH ROW BEGIN
  UPDATE requests r
    JOIN (SELECT COALESCE(SUM(qty*unit_price),0) t FROM request_items WHERE request_id = OLD.request_id) x
      ON 1=1
  SET r.total_amount = x.t
  WHERE r.id = OLD.request_id;
END//
DELIMITER ;

INSERT INTO departments (name) VALUES ('Comercial'), ('Compras'), ('T.I'), ('Industrial')
ON DUPLICATE KEY UPDATE name=VALUES(name);
