
-- db/init.sql
CREATE TABLE IF NOT EXISTS departments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS suppliers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  ruc VARCHAR(20) NULL,
  contact_name VARCHAR(120) NULL,
  email VARCHAR(120) NULL,
  phone VARCHAR(40) NULL,
  address VARCHAR(255) NULL,
  bank_account VARCHAR(120) NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS cost_centers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(160) NOT NULL,
  department_id INT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE IF NOT EXISTS budgets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cost_center_id INT NOT NULL,
  year INT NOT NULL,
  allocated DECIMAL(12,2) NOT NULL DEFAULT 0,
  committed DECIMAL(12,2) NOT NULL DEFAULT 0,
  spent DECIMAL(12,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY (cost_center_id, year),
  FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id)
);

CREATE TABLE IF NOT EXISTS purchase_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  requester_id INT NOT NULL,
  department_id INT NULL,
  cost_center_id INT NULL,
  supplier_id INT NULL,
  title VARCHAR(200) NOT NULL,
  justification TEXT NULL,
  priority ENUM('Baja','Media','Alta','Crítica') NOT NULL DEFAULT 'Media',
  expected_date DATE NULL,
  status ENUM('Draft','Submitted','MgrApproved','FinApproved','Rejected','Ordered','Received','Closed','Canceled') NOT NULL DEFAULT 'Submitted',
  total_estimated DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL,
  FOREIGN KEY (requester_id) REFERENCES users(id),
  FOREIGN KEY (department_id) REFERENCES departments(id),
  FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id),
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE IF NOT EXISTS request_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) NOT NULL,
  unit_price DECIMAL(12,2) NOT NULL,
  asset_flag TINYINT(1) NOT NULL DEFAULT 0,
  category VARCHAR(120) NULL,
  account_code VARCHAR(40) NULL,
  FOREIGN KEY (request_id) REFERENCES purchase_requests(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS approvals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  step ENUM('Manager','Finance') NOT NULL,
  approver_id INT NULL,
  decision ENUM('Pending','Approved','Rejected') NOT NULL DEFAULT 'Pending',
  decision_at TIMESTAMP NULL,
  comments TEXT NULL,
  FOREIGN KEY (request_id) REFERENCES purchase_requests(id)
);

CREATE TABLE IF NOT EXISTS documents (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  uploader_id INT NOT NULL,
  filename VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NULL,
  size INT NULL,
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES purchase_requests(id),
  FOREIGN KEY (uploader_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  user_id INT NOT NULL,
  comment TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (request_id) REFERENCES purchase_requests(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS status_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  request_id INT NOT NULL,
  from_status VARCHAR(30) NULL,
  to_status VARCHAR(30) NOT NULL,
  changed_by INT NOT NULL,
  changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  note VARCHAR(255) NULL,
  FOREIGN KEY (request_id) REFERENCES purchase_requests(id),
  FOREIGN KEY (changed_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  action VARCHAR(60) NOT NULL,
  entity VARCHAR(60) NOT NULL,
  entity_id INT NULL,
  details TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Datos de ejemplo (contraseñas: "123456")
INSERT INTO departments (name) VALUES ('Tecnología'),('Finanzas');

INSERT INTO suppliers (name,ruc,email,phone) VALUES ('Proveedor A','0999999999','ventas@proveedora.com','022222222');
INSERT INTO cost_centers (code,name,department_id) VALUES ('CC-TEK','Tecnología General',1);
INSERT INTO budgets (cost_center_id, year, allocated, committed, spent) VALUES (1, YEAR(CURDATE()), 50000, 0, 0);
