CREATE DATABASE IF NOT EXISTS cola_fondos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE cola_fondos;
CREATE TABLE users(id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(80) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('ADMIN','TESORERO','PARTICIPANTE') NOT NULL DEFAULT 'PARTICIPANTE',participant_id INT NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE participants(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,phone VARCHAR(40),email VARCHAR(150),active TINYINT(1) DEFAULT 1,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE cash_boxes(id INT AUTO_INCREMENT PRIMARY KEY,participant_id INT NOT NULL UNIQUE,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(participant_id) REFERENCES participants(id));
CREATE TABLE activities(id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,event_date DATE NOT NULL,description TEXT,status VARCHAR(30) DEFAULT 'ABIERTA',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE goals(id INT AUTO_INCREMENT PRIMARY KEY,activity_id INT NULL,target_amount DECIMAL(14,2) NOT NULL,active TINYINT(1) DEFAULT 1,FOREIGN KEY(activity_id) REFERENCES activities(id));
CREATE TABLE movements(id INT AUTO_INCREMENT PRIMARY KEY,participant_id INT NOT NULL,activity_id INT NULL,type ENUM('INGRESO','ENTREGA','GASTO') NOT NULL,concept VARCHAR(255) NOT NULL,amount DECIMAL(14,2) NOT NULL,status ENUM('CONFIRMADO','PENDIENTE') DEFAULT 'CONFIRMADO',user_id INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(participant_id) REFERENCES participants(id),FOREIGN KEY(activity_id) REFERENCES activities(id),FOREIGN KEY(user_id) REFERENCES users(id));
CREATE TABLE ticket_batches(id INT AUTO_INCREMENT PRIMARY KEY,activity_id INT NOT NULL,participant_id INT NOT NULL,start_no INT NOT NULL,end_no INT NOT NULL,unit_price DECIMAL(14,2) NOT NULL,status VARCHAR(30) DEFAULT 'ASIGNADO',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(activity_id) REFERENCES activities(id),FOREIGN KEY(participant_id) REFERENCES participants(id));
CREATE TABLE audit_log(id BIGINT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,action VARCHAR(80) NOT NULL,entity VARCHAR(80),entity_id INT,details TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES users(id));
INSERT INTO users(username,password_hash,role) VALUES('admin','$2y$10$92IXUNpkjO0rQ5byMi.Ye4oKoEa3Ro9llC2w1K9K2b2L0m3dV7O','ADMIN');

CREATE TABLE meetings(
 id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(180) NOT NULL,meeting_date DATETIME NOT NULL,
 location VARCHAR(180),description TEXT,created_by INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id)
);
CREATE TABLE meeting_documents(
 id INT AUTO_INCREMENT PRIMARY KEY,meeting_id INT NOT NULL,original_name VARCHAR(255) NOT NULL,
 stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120),file_size INT,uploaded_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(uploaded_by) REFERENCES users(id)
);
CREATE TABLE participant_documents(
 id INT AUTO_INCREMENT PRIMARY KEY,participant_id INT NOT NULL,
 document_type ENUM('CEDULA_FRENTE','CEDULA_DORSO','DOCUMENTO_FIRMADO','AUTORIZACION','OTRO') NOT NULL DEFAULT 'OTRO',
 description VARCHAR(255),original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,
 mime_type VARCHAR(120),file_size INT,uploaded_by INT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE,
 FOREIGN KEY(uploaded_by) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS meeting_attendance(
 id INT AUTO_INCREMENT PRIMARY KEY,
 meeting_id INT NOT NULL,
 participant_id INT NOT NULL,
 attended TINYINT(1) NOT NULL DEFAULT 1,
 notes VARCHAR(255),
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_attendance(meeting_id,participant_id),
 FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS meeting_tasks(
 id INT AUTO_INCREMENT PRIMARY KEY,
 meeting_id INT NOT NULL,
 participant_id INT NULL,
 title VARCHAR(220) NOT NULL,
 description TEXT,
 due_date DATE NULL,
 status ENUM('PENDIENTE','EN_PROCESO','CUMPLIDA') NOT NULL DEFAULT 'PENDIENTE',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(meeting_id) REFERENCES meetings(id) ON DELETE CASCADE,
 FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS participant_notes(
 id INT AUTO_INCREMENT PRIMARY KEY,
 participant_id INT NOT NULL,
 note TEXT NOT NULL,
 created_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(participant_id) REFERENCES participants(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id)
);