ALTER TABLE branches ADD COLUMN IF NOT EXISTS esign_expire_hours INT NULL;
CREATE TABLE IF NOT EXISTS form_templates (template_id INT AUTO_INCREMENT PRIMARY KEY, template_name VARCHAR(100) NOT NULL, description TEXT, file_path VARCHAR(255) NOT NULL, version VARCHAR(20) DEFAULT '1.0', branch_scope ENUM('ALL','BRANCH','DISTRICT') DEFAULT 'ALL', expire_hours INT DEFAULT 48, active TINYINT(1) DEFAULT 1, created_by VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS form_fields (field_id INT AUTO_INCREMENT PRIMARY KEY, template_id INT NOT NULL, field_name VARCHAR(100) NOT NULL, field_label VARCHAR(255), field_type ENUM('text','date','select','checkbox','signature') DEFAULT 'text', required TINYINT(1) DEFAULT 0, options_json TEXT, position_json TEXT, FOREIGN KEY (template_id) REFERENCES form_templates(template_id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS form_sessions (session_id INT AUTO_INCREMENT PRIMARY KEY, case_id INT NOT NULL, branch_id INT NOT NULL, user_id INT NOT NULL, template_ids TEXT, status ENUM('in_progress','awaiting_signature','signed','archived') DEFAULT 'in_progress', output_path VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS form_data (data_id INT AUTO_INCREMENT PRIMARY KEY, session_id INT NOT NULL, field_id INT NOT NULL, field_value TEXT, FOREIGN KEY (session_id) REFERENCES form_sessions(session_id) ON DELETE CASCADE, FOREIGN KEY (field_id) REFERENCES form_fields(field_id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS esign_sessions (esign_id INT AUTO_INCREMENT PRIMARY KEY, session_id INT NOT NULL, signer_name VARCHAR(150), signer_email VARCHAR(150), signer_role VARCHAR(100), token VARCHAR(64) UNIQUE, status ENUM('pending','signed','expired','declined') DEFAULT 'pending', signed_at DATETIME DEFAULT NULL, expires_at DATETIME DEFAULT NULL, expire_hours INT DEFAULT 48, ip_address VARCHAR(45), signature_image VARCHAR(255), verification_code VARCHAR(8), FOREIGN KEY (session_id) REFERENCES form_sessions(session_id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS form_archive (archive_id INT AUTO_INCREMENT PRIMARY KEY, session_id INT NOT NULL, case_id INT NOT NULL, branch_id INT NOT NULL, file_path VARCHAR(255) NOT NULL, signer_name VARCHAR(150), signed_at DATETIME, hash_sha256 CHAR(64), stored_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
