CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','tu','direktur','wadir','unit') NOT NULL DEFAULT 'unit', unit VARCHAR(120) DEFAULT NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE letters (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, direction ENUM('in','out') NOT NULL, number VARCHAR(160) DEFAULT NULL, subject VARCHAR(255) NOT NULL, correspondent VARCHAR(255) NOT NULL, letter_date DATE NOT NULL, received_date DATE DEFAULT NULL, classification VARCHAR(40) DEFAULT NULL, status ENUM('draft','submitted','approved','rejected','archived') NOT NULL DEFAULT 'draft', description TEXT, created_by BIGINT UNSIGNED NOT NULL, approved_by BIGINT UNSIGNED DEFAULT NULL, approved_at DATETIME DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(direction,status), INDEX(letter_date), FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(approved_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE attachments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, letter_id BIGINT UNSIGNED NOT NULL, original_name VARCHAR(255) NOT NULL, mime VARCHAR(120) NOT NULL, bytes BIGINT UNSIGNED NOT NULL, local_path VARCHAR(255) DEFAULT NULL, drive_id VARCHAR(200) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(letter_id) REFERENCES letters(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE dispositions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, letter_id BIGINT UNSIGNED NOT NULL, from_user BIGINT UNSIGNED NOT NULL, to_user BIGINT UNSIGNED NOT NULL, instruction TEXT NOT NULL, due_date DATE DEFAULT NULL, status ENUM('open','done') DEFAULT 'open', response TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME DEFAULT NULL, FOREIGN KEY(letter_id) REFERENCES letters(id) ON DELETE CASCADE, FOREIGN KEY(from_user) REFERENCES users(id), FOREIGN KEY(to_user) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE numbering (year SMALLINT UNSIGNED NOT NULL, classification VARCHAR(40) NOT NULL, last_number INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(year,classification)) ENGINE=InnoDB;
CREATE TABLE audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED DEFAULT NULL, action VARCHAR(80) NOT NULL, entity VARCHAR(50) NOT NULL, entity_id BIGINT UNSIGNED DEFAULT NULL, ip VARCHAR(45) DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(entity,entity_id)) ENGINE=InnoDB;