CREATE TABLE IF NOT EXISTS tenders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 tender_number VARCHAR(32) NOT NULL UNIQUE,
 title VARCHAR(255) NOT NULL,
 client VARCHAR(255) NOT NULL,
 project VARCHAR(255) NULL,
 rfp_number VARCHAR(100) NULL,
 tender_type VARCHAR(50) NOT NULL DEFAULT 'RFP',
 location VARCHAR(255) NULL,
 source_url TEXT NULL,
 issue_date DATE NULL,
 closing_at DATETIME NULL,
 closing_timezone VARCHAR(64) NULL,
 questions_deadline DATETIME NULL,
 site_visit_at DATETIME NULL,
 estimated_value DECIMAL(15,2) NULL,
 probability TINYINT UNSIGNED NULL,
 owner_name VARCHAR(160) NULL,
 owner_email VARCHAR(190) NULL,
 status VARCHAR(50) NOT NULL DEFAULT 'Identified',
 priority VARCHAR(20) NOT NULL DEFAULT 'Medium',
 sharepoint_url TEXT NULL,
 notes TEXT NULL,
 created_by VARCHAR(190) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(status), INDEX(closing_at), INDEX(client)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_requirements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,
 requirement_number VARCHAR(64) NULL,source_document VARCHAR(255) NULL,source_page VARCHAR(32) NULL,source_section VARCHAR(128) NULL,
 requirement_text TEXT NOT NULL,requirement_type VARCHAR(64) NOT NULL DEFAULT 'Technical',mandatory TINYINT(1) NOT NULL DEFAULT 0,scored TINYINT(1) NOT NULL DEFAULT 0,weight DECIMAL(6,2) NULL,
 owner_name VARCHAR(160) NULL,due_date DATE NULL,response_text TEXT NULL,evidence_url TEXT NULL,proposal_section VARCHAR(160) NULL,status VARCHAR(40) NOT NULL DEFAULT 'Not Reviewed',reviewer VARCHAR(160) NULL,reviewer_comments TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_req_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE, INDEX(tender_id,status), INDEX(mandatory)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_addenda (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,addendum_number VARCHAR(64) NOT NULL,received_date DATE NULL,document_url TEXT NULL,summary TEXT NULL,closing_changed TINYINT(1) NOT NULL DEFAULT 0,new_closing_at DATETIME NULL,ack_required TINYINT(1) NOT NULL DEFAULT 0,acknowledged_by VARCHAR(160) NULL,acknowledged_at DATETIME NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,CONSTRAINT fk_add_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_questions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,question_number VARCHAR(64) NULL,category VARCHAR(80) NULL,question TEXT NOT NULL,submitted_date DATE NULL,response_due DATE NULL,client_response TEXT NULL,response_date DATE NULL,impact TEXT NULL,status VARCHAR(32) NOT NULL DEFAULT 'Draft',created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_q_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_proposal_sections (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,title VARCHAR(190) NOT NULL,sort_order INT NOT NULL DEFAULT 0,owner_name VARCHAR(160) NULL,status VARCHAR(32) NOT NULL DEFAULT 'Not Started',content MEDIUMTEXT NULL,updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 CONSTRAINT fk_prop_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,review_type VARCHAR(80) NOT NULL,reviewer VARCHAR(160) NOT NULL,due_date DATE NULL,decision VARCHAR(50) NULL,comments TEXT NULL,completed_at DATETIME NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_review_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,submitted_at DATETIME NULL,timezone VARCHAR(64) NULL,submitted_by VARCHAR(160) NULL,submission_method VARCHAR(80) NULL,portal VARCHAR(190) NULL,commercial_amount DECIMAL(15,2) NULL,estimate_revision VARCHAR(64) NULL,proposal_revision VARCHAR(64) NULL,schedule_revision VARCHAR(64) NULL,confirmation_number VARCHAR(120) NULL,proof_url TEXT NULL,notes TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_sub_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_checklist (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL,item_key VARCHAR(120) NOT NULL,label VARCHAR(255) NOT NULL,mandatory TINYINT(1) NOT NULL DEFAULT 1,complete TINYINT(1) NOT NULL DEFAULT 0,completed_by VARCHAR(160) NULL,completed_at DATETIME NULL,UNIQUE KEY uq_check(tender_id,item_key),CONSTRAINT fk_check_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_results (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tender_id BIGINT UNSIGNED NOT NULL UNIQUE,result VARCHAR(32) NOT NULL,award_date DATE NULL,award_amount DECIMAL(15,2) NULL,contract_number VARCHAR(120) NULL,successful_bidder VARCHAR(190) NULL,winning_price DECIMAL(15,2) NULL,reason_lost VARCHAR(190) NULL,lessons_learned TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT fk_result_tender FOREIGN KEY(tender_id) REFERENCES tenders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tender_activity (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_email VARCHAR(190) NULL,action VARCHAR(120) NOT NULL,entity_type VARCHAR(80) NOT NULL,entity_id BIGINT NULL,details TEXT NULL,created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,INDEX(created_at),INDEX(entity_type,entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;