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

CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role VARCHAR(50) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS budget_items (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(60) NOT NULL UNIQUE,
 name VARCHAR(180) NOT NULL,
 total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
 year SMALLINT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchases (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(40) NOT NULL UNIQUE,
 title VARCHAR(255) NOT NULL,
 unit_name VARCHAR(180) NOT NULL,
 requester_name VARCHAR(180) NOT NULL,
 purchase_type VARCHAR(40) NOT NULL DEFAULT 'producto',
 need_description TEXT NULL,
 estimated_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
 budget_item_id INT UNSIGNED NULL,
 specifications LONGTEXT NULL,
 certifications TEXT NULL,
 required_date DATE NULL,
 delivery_mode VARCHAR(50) NULL,
 max_budget DECIMAL(15,2) NULL,
 duration_months INT NULL,
 state VARCHAR(60) NOT NULL DEFAULT 'draft',
 current_version INT NOT NULL DEFAULT 0,
 created_by INT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX idx_purchase_state(state), INDEX idx_budget_item(budget_item_id),
 CONSTRAINT fk_purchase_budget FOREIGN KEY (budget_item_id) REFERENCES budget_items(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS purchase_documents (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_id INT UNSIGNED NOT NULL,
 doc_type VARCHAR(70) NOT NULL,
 version INT NOT NULL,
 original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(150) NULL,
 size_bytes BIGINT NULL,
 uploaded_by INT UNSIGNED NULL,
 uploaded_name VARCHAR(180) NULL,
 note TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_docs_purchase(purchase_id),
 CONSTRAINT fk_docs_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_id INT UNSIGNED NOT NULL,
 user_id INT UNSIGNED NULL,
 user_name VARCHAR(180) NOT NULL,
 action VARCHAR(160) NOT NULL,
 detail TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_audit_purchase(purchase_id),
 CONSTRAINT fk_audit_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS observations (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_id INT UNSIGNED NOT NULL,
 user_id INT UNSIGNED NULL,
 user_name VARCHAR(180) NOT NULL,
 category VARCHAR(100) NULL,
 text TEXT NOT NULL,
 resolved TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 INDEX idx_obs_purchase(purchase_id),
 CONSTRAINT fk_obs_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS budget_certificates (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 purchase_id INT UNSIGNED NOT NULL UNIQUE,
 requested_by INT UNSIGNED NULL,
 status VARCHAR(60) NOT NULL DEFAULT 'requested',
 certificate_number VARCHAR(120) NULL,
 file_document_id INT UNSIGNED NULL,
 requested_at DATETIME NOT NULL,
 approved_at DATETIME NULL,
 CONSTRAINT fk_cert_purchase FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE
) ENGINE=InnoDB;