CREATE TABLE IF NOT EXISTS partner_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    project_id INT DEFAULT NULL,
    partner_id INT DEFAULT NULL,
    title VARCHAR(180) NOT NULL,
    reporting_period VARCHAR(100) DEFAULT '',
    summary TEXT NULL,
    evidence_path VARCHAR(255) DEFAULT '',
    status VARCHAR(40) DEFAULT 'submitted',
    reviewer_notes TEXT NULL,
    submitted_by INT DEFAULT 0,
    reviewed_by INT DEFAULT 0,
    submitted_at DATETIME NULL,
    reviewed_at DATETIME NULL,
    INDEX idx_partner_reports_tenant_status (tenant_id, status),
    INDEX idx_partner_reports_project (project_id),
    INDEX idx_partner_reports_partner (partner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (id, module, name, slug) VALUES
(41, 'partner_reports', 'View partner reports', 'partner_reports.view'),
(42, 'partner_reports', 'Manage partner reports', 'partner_reports.manage');

INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(2,41),(2,42),
(3,41),(3,42),
(4,41),
(9,41),
(10,41),(10,42),
(11,41);