SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS schema_migrations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 migration_name VARCHAR(190) NOT NULL UNIQUE,
 checksum_sha256 CHAR(64) NOT NULL,
 applied_at DATETIME(6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 auth_source ENUM('sso','local_emergency') NOT NULL,
 sso_sub VARCHAR(190) NULL UNIQUE,
 full_name VARCHAR(190) NOT NULL,
 preferred_name VARCHAR(120) NULL,
 professional_email VARCHAR(190) NULL,
 professional_phone VARCHAR(80) NULL,
 employee_id VARCHAR(120) NULL,
 person_id VARCHAR(120) NULL,
 job_title VARCHAR(190) NULL,
 org_unit VARCHAR(190) NULL,
 status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX idx_users_status(status), INDEX idx_users_employee(employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS emergency_credentials (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE,
 username VARCHAR(120) NOT NULL UNIQUE,
 singleton_guard TINYINT UNSIGNED NOT NULL DEFAULT 1 UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 enabled TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 rotated_at DATETIME NULL,
 CONSTRAINT fk_emergency_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 session_hash CHAR(64) NOT NULL UNIQUE,
 user_id BIGINT UNSIGNED NOT NULL,
 auth_source ENUM('sso','local_emergency') NOT NULL,
 sso_sub VARCHAR(190) NULL,
 created_at DATETIME NOT NULL,
 last_seen_at DATETIME NOT NULL,
 revoked_at DATETIME NULL,
 revoked_reason VARCHAR(120) NULL,
 INDEX idx_auth_sessions_user(user_id,revoked_at), INDEX idx_auth_sessions_sub(sso_sub,revoked_at),
 CONSTRAINT fk_auth_sessions_user FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(100) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL,
 system_role TINYINT(1) NOT NULL DEFAULT 1, active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS permissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(120) NOT NULL UNIQUE, description VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS role_permissions (
 role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL,
 PRIMARY KEY(role_id,permission_id),
 FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(permission_id) REFERENCES permissions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS user_role_assignments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('global','team','portfolio','self') NOT NULL DEFAULT 'self', scope_id BIGINT UNSIGNED NULL,
 assigned_by BIGINT UNSIGNED NULL, assigned_at DATETIME NOT NULL, revoked_at DATETIME NULL, reason VARCHAR(255) NULL,
 INDEX idx_ura_user(user_id,revoked_at),
 FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teams (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(80) NOT NULL UNIQUE, name VARCHAR(190) NOT NULL,
 manager_user_id BIGINT UNSIGNED NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL,
 FOREIGN KEY(manager_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS team_members (
 team_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,valid_from DATE NOT NULL,valid_to DATE NULL,
 PRIMARY KEY(team_id,user_id,valid_from), FOREIGN KEY(team_id) REFERENCES teams(id),FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS portfolios (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,
 team_id BIGINT UNSIGNED NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL,FOREIGN KEY(team_id) REFERENCES teams(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS portfolio_members (
 portfolio_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,valid_from DATE NOT NULL,valid_to DATE NULL,
 PRIMARY KEY(portfolio_id,user_id,valid_from),FOREIGN KEY(portfolio_id) REFERENCES portfolios(id),FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_sources (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,branch_name VARCHAR(190) NULL,name VARCHAR(190) NOT NULL,
 short_description TEXT NULL,target_audience TEXT NULL,key_arguments TEXT NULL,faq_text LONGTEXT NULL,
 status ENUM('draft','active','inactive') NOT NULL DEFAULT 'draft',valid_from DATE NULL,valid_to DATE NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS campaigns (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,product_id BIGINT UNSIGNED NULL,
 segment VARCHAR(190) NULL,starts_at DATETIME NULL,ends_at DATETIME NULL,landing_page VARCHAR(500) NULL,channel VARCHAR(120) NULL,
 tracking_code VARCHAR(120) NULL UNIQUE,cost DECIMAL(18,2) NULL,currency CHAR(3) NULL DEFAULT 'AOA',status ENUM('draft','active','paused','closed') NOT NULL DEFAULT 'draft',
 created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,full_name VARCHAR(190) NOT NULL,
 professional_email VARCHAR(190) NULL,professional_phone VARCHAR(80) NULL,preferred_channel VARCHAR(80) NULL,
 contactability_status ENUM('unknown','provided','allowed','restricted','revoked') NOT NULL DEFAULT 'unknown',province VARCHAR(120) NULL,locality VARCHAR(190) NULL,owner_user_id BIGINT UNSIGNED NULL,team_id BIGINT UNSIGNED NULL,portfolio_id BIGINT UNSIGNED NULL,
 merged_into_contact_id BIGINT UNSIGNED NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX idx_contacts_email(professional_email),INDEX idx_contacts_phone(professional_phone),INDEX idx_contacts_scope(owner_user_id,team_id,portfolio_id),FOREIGN KEY(owner_user_id) REFERENCES users(id),FOREIGN KEY(team_id) REFERENCES teams(id),FOREIGN KEY(portfolio_id) REFERENCES portfolios(id),FOREIGN KEY(merged_into_contact_id) REFERENCES contacts(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS organizations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,tax_id VARCHAR(80) NULL,sector VARCHAR(190) NULL,
 province VARCHAR(120) NULL,locality VARCHAR(190) NULL,owner_user_id BIGINT UNSIGNED NULL,team_id BIGINT UNSIGNED NULL,portfolio_id BIGINT UNSIGNED NULL,merged_into_organization_id BIGINT UNSIGNED NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX idx_org_name(name),INDEX idx_org_tax(tax_id),INDEX idx_org_scope(owner_user_id,team_id,portfolio_id),FOREIGN KEY(owner_user_id) REFERENCES users(id),FOREIGN KEY(team_id) REFERENCES teams(id),FOREIGN KEY(portfolio_id) REFERENCES portfolios(id),FOREIGN KEY(merged_into_organization_id) REFERENCES organizations(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ambassador_referrals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,gch_referral_reference VARCHAR(120) NOT NULL UNIQUE,
 prospect_name VARCHAR(190) NOT NULL,prospect_phone VARCHAR(80) NULL,prospect_email VARCHAR(190) NULL,product_interest VARCHAR(190) NULL,
 contactability VARCHAR(80) NOT NULL,consent_purpose VARCHAR(255) NULL,authorized_observation TEXT NULL,ambassador_employee_id VARCHAR(120) NOT NULL,
 campaign_origin VARCHAR(190) NULL,public_status ENUM('received','in_contact','quoted','converted','not_converted') NOT NULL DEFAULT 'received',
 source_timestamp DATETIME NOT NULL,received_at DATETIME NOT NULL,lead_id BIGINT UNSIGNED NULL,last_status_sent_at DATETIME NULL,INDEX idx_amb_employee(ambassador_employee_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS leads (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,phone VARCHAR(80) NULL,email VARCHAR(190) NULL,
 source_id BIGINT UNSIGNED NULL,product_id BIGINT UNSIGNED NULL,preferred_channel VARCHAR(80) NULL,consent_status ENUM('unknown','provided','allowed','restricted','revoked') NOT NULL DEFAULT 'unknown',
 consent_purpose VARCHAR(255) NULL,province VARCHAR(120) NULL,locality VARCHAR(190) NULL,owner_user_id BIGINT UNSIGNED NULL,team_id BIGINT UNSIGNED NULL,portfolio_id BIGINT UNSIGNED NULL,
 priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',qualification_score TINYINT UNSIGNED NOT NULL DEFAULT 0,qualification_explanation TEXT NULL,status VARCHAR(80) NOT NULL DEFAULT 'new',
 first_contact_at DATETIME NULL,last_activity_at DATETIME NULL,next_action_at DATETIME NULL,ambassador_referral_id BIGINT UNSIGNED NULL,merged_into_lead_id BIGINT UNSIGNED NULL,
 created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX idx_leads_owner(owner_user_id,status),INDEX idx_leads_team(team_id,status),INDEX idx_leads_portfolio(portfolio_id,status),INDEX idx_leads_email(email),INDEX idx_leads_phone(phone),
 FOREIGN KEY(source_id) REFERENCES lead_sources(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(owner_user_id) REFERENCES users(id),FOREIGN KEY(team_id) REFERENCES teams(id),
 FOREIGN KEY(portfolio_id) REFERENCES portfolios(id),FOREIGN KEY(ambassador_referral_id) REFERENCES ambassador_referrals(id),FOREIGN KEY(merged_into_lead_id) REFERENCES leads(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS lead_state_definitions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,canonical_status ENUM('open','converted','lost') NOT NULL DEFAULT 'open',
 position INT NOT NULL,active TINYINT(1) NOT NULL DEFAULT 1,immutable_semantic_key VARCHAR(80) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS lead_state_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,lead_id BIGINT UNSIGNED NOT NULL,from_status VARCHAR(80) NULL,to_status VARCHAR(80) NOT NULL,reason VARCHAR(255) NULL,
 changed_by BIGINT UNSIGNED NULL,changed_at DATETIME NOT NULL,INDEX idx_lead_history(lead_id,changed_at),FOREIGN KEY(lead_id) REFERENCES leads(id),FOREIGN KEY(changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS duplicate_candidates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,entity_type ENUM('lead','contact','organization') NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,candidate_entity_id BIGINT UNSIGNED NOT NULL,
 match_reason VARCHAR(120) NOT NULL,confidence DECIMAL(5,2) NULL,status ENUM('open','confirmed','dismissed','merged') NOT NULL DEFAULT 'open',reviewed_by BIGINT UNSIGNED NULL,reviewed_at DATETIME NULL,created_at DATETIME NOT NULL,
 UNIQUE KEY uq_duplicate_pair(entity_type,entity_id,candidate_entity_id),FOREIGN KEY(reviewed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS merge_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,entity_type ENUM('lead','contact','organization') NOT NULL,source_entity_id BIGINT UNSIGNED NOT NULL,target_entity_id BIGINT UNSIGNED NOT NULL,
 reason VARCHAR(255) NOT NULL,snapshot_json JSON NOT NULL,merged_by BIGINT UNSIGNED NOT NULL,merged_at DATETIME NOT NULL,FOREIGN KEY(merged_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(80) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,semantic_key VARCHAR(80) NOT NULL UNIQUE,
 canonical_status ENUM('open','won','lost') NOT NULL DEFAULT 'open',position INT NOT NULL,default_probability TINYINT UNSIGNED NOT NULL DEFAULT 0,active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS opportunities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,title VARCHAR(255) NOT NULL,contact_id BIGINT UNSIGNED NULL,organization_id BIGINT UNSIGNED NULL,
 product_id BIGINT UNSIGNED NULL,source_id BIGINT UNSIGNED NULL,campaign_id BIGINT UNSIGNED NULL,ambassador_referral_id BIGINT UNSIGNED NULL,owner_user_id BIGINT UNSIGNED NULL,team_id BIGINT UNSIGNED NULL,portfolio_id BIGINT UNSIGNED NULL,
 stage_id BIGINT UNSIGNED NOT NULL,canonical_status ENUM('open','won','lost') NOT NULL DEFAULT 'open',potential_value DECIMAL(18,2) NULL,currency CHAR(3) NOT NULL DEFAULT 'AOA',probability TINYINT UNSIGNED NOT NULL DEFAULT 0,
 estimated_close_date DATE NULL,last_activity_at DATETIME NULL,next_activity_at DATETIME NULL,won_reason VARCHAR(255) NULL,loss_reason VARCHAR(255) NULL,notes TEXT NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX idx_opp_owner(owner_user_id,canonical_status),INDEX idx_opp_stage(stage_id,canonical_status),INDEX idx_opp_close(estimated_close_date),
 FOREIGN KEY(contact_id) REFERENCES contacts(id),FOREIGN KEY(organization_id) REFERENCES organizations(id),FOREIGN KEY(product_id) REFERENCES products(id),FOREIGN KEY(source_id) REFERENCES lead_sources(id),FOREIGN KEY(campaign_id) REFERENCES campaigns(id),
 FOREIGN KEY(ambassador_referral_id) REFERENCES ambassador_referrals(id),FOREIGN KEY(owner_user_id) REFERENCES users(id),FOREIGN KEY(team_id) REFERENCES teams(id),FOREIGN KEY(portfolio_id) REFERENCES portfolios(id),FOREIGN KEY(stage_id) REFERENCES pipeline_stages(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS opportunity_stage_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,opportunity_id BIGINT UNSIGNED NOT NULL,from_stage_id BIGINT UNSIGNED NULL,to_stage_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(255) NULL,changed_by BIGINT UNSIGNED NULL,changed_at DATETIME NOT NULL,
 FOREIGN KEY(opportunity_id) REFERENCES opportunities(id),FOREIGN KEY(from_stage_id) REFERENCES pipeline_stages(id),FOREIGN KEY(to_stage_id) REFERENCES pipeline_stages(id),FOREIGN KEY(changed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activities (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,type ENUM('call','meeting','email','whatsapp_reference','note','task','follow_up','document_sent','document_received','quote','stage_change','assignment','sla','escalation') NOT NULL,
 subject VARCHAR(255) NOT NULL,details TEXT NULL,lead_id BIGINT UNSIGNED NULL,opportunity_id BIGINT UNSIGNED NULL,contact_id BIGINT UNSIGNED NULL,organization_id BIGINT UNSIGNED NULL,occurred_at DATETIME NOT NULL,next_action_at DATETIME NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,
 INDEX idx_activities_lead(lead_id,occurred_at),INDEX idx_activities_opp(opportunity_id,occurred_at),FOREIGN KEY(lead_id) REFERENCES leads(id),FOREIGN KEY(opportunity_id) REFERENCES opportunities(id),FOREIGN KEY(contact_id) REFERENCES contacts(id),FOREIGN KEY(organization_id) REFERENCES organizations(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,title VARCHAR(255) NOT NULL,description TEXT NULL,assigned_user_id BIGINT UNSIGNED NULL,team_id BIGINT UNSIGNED NULL,lead_id BIGINT UNSIGNED NULL,opportunity_id BIGINT UNSIGNED NULL,
 due_at DATETIME NULL,priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',status ENUM('open','in_progress','done','cancelled') NOT NULL DEFAULT 'open',completed_at DATETIME NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 INDEX idx_tasks_assignee(assigned_user_id,status,due_at),FOREIGN KEY(assigned_user_id) REFERENCES users(id),FOREIGN KEY(team_id) REFERENCES teams(id),FOREIGN KEY(lead_id) REFERENCES leads(id),FOREIGN KEY(opportunity_id) REFERENCES opportunities(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS quotes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(120) NOT NULL,opportunity_id BIGINT UNSIGNED NOT NULL,external_reference VARCHAR(190) NULL,status ENUM('draft','requested','received','sent','accepted','declined','expired') NOT NULL DEFAULT 'draft',quoted_value DECIMAL(18,2) NULL,currency CHAR(3) NOT NULL DEFAULT 'AOA',valid_until DATE NULL,created_by BIGINT UNSIGNED NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,
 FOREIGN KEY(opportunity_id) REFERENCES opportunities(id),FOREIGN KEY(created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sla_policies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(100) NOT NULL UNIQUE,name VARCHAR(190) NOT NULL,entity_type ENUM('lead','opportunity','task') NOT NULL,trigger_event VARCHAR(120) NOT NULL,target_minutes INT UNSIGNED NOT NULL,warning_before_minutes INT UNSIGNED NULL,escalation_role_code VARCHAR(100) NULL,priority INT NOT NULL DEFAULT 100,explanation TEXT NULL,active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sla_instances (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,policy_id BIGINT UNSIGNED NOT NULL,entity_type ENUM('lead','opportunity','task') NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,status ENUM('running','met','breached','cancelled') NOT NULL DEFAULT 'running',started_at DATETIME NOT NULL,due_at DATETIME NOT NULL,satisfied_at DATETIME NULL,breached_at DATETIME NULL,explanation_json JSON NULL,created_at DATETIME NOT NULL,
 INDEX idx_sla_due(status,due_at),INDEX idx_sla_entity(entity_type,entity_id),FOREIGN KEY(policy_id) REFERENCES sla_policies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sla_escalations (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,sla_instance_id BIGINT UNSIGNED NOT NULL,level_no INT UNSIGNED NOT NULL,target_role_code VARCHAR(100) NULL,target_user_id BIGINT UNSIGNED NULL,reason VARCHAR(255) NOT NULL,escalated_at DATETIME NOT NULL,acknowledged_at DATETIME NULL,FOREIGN KEY(sla_instance_id) REFERENCES sla_instances(id),FOREIGN KEY(target_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(80) NOT NULL UNIQUE,entity_type VARCHAR(80) NOT NULL,entity_id BIGINT UNSIGNED NOT NULL,original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL UNIQUE,mime_type VARCHAR(190) NOT NULL,size_bytes BIGINT UNSIGNED NOT NULL,sha256 CHAR(64) NOT NULL,private_path VARCHAR(500) NOT NULL,uploaded_by BIGINT UNSIGNED NULL,uploaded_at DATETIME NOT NULL,deleted_at DATETIME NULL,
 INDEX idx_attachments_entity(entity_type,entity_id),FOREIGN KEY(uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS notifications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,type VARCHAR(100) NOT NULL,title VARCHAR(255) NOT NULL,body TEXT NULL,link VARCHAR(500) NULL,created_at DATETIME NOT NULL,read_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id),INDEX idx_notifications_user(user_id,read_at,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS webpush_subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id BIGINT UNSIGNED NOT NULL,endpoint_hash CHAR(64) NOT NULL UNIQUE,endpoint_encrypted TEXT NOT NULL,p256dh_encrypted TEXT NOT NULL,auth_encrypted TEXT NOT NULL,created_at DATETIME NOT NULL,revoked_at DATETIME NULL,FOREIGN KEY(user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_nonces (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,namespace VARCHAR(100) NOT NULL,nonce_hash CHAR(64) NOT NULL,seen_at DATETIME NOT NULL,UNIQUE KEY uq_nonce(namespace,nonce_hash),INDEX idx_nonce_seen(seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS integration_idempotency (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,integration VARCHAR(100) NOT NULL,idempotency_hash CHAR(64) NOT NULL,resource_type VARCHAR(100) NOT NULL,resource_id BIGINT UNSIGNED NOT NULL,created_at DATETIME NOT NULL,UNIQUE KEY uq_idempotency(integration,idempotency_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS integration_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,integration VARCHAR(100) NOT NULL,direction ENUM('in','out') NOT NULL,event_type VARCHAR(120) NOT NULL,correlation_id VARCHAR(120) NULL,status VARCHAR(80) NOT NULL,http_status SMALLINT UNSIGNED NULL,payload_sha256 CHAR(64) NULL,error_code VARCHAR(120) NULL,created_at DATETIME NOT NULL,INDEX idx_integration_logs(integration,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS rate_limit_buckets (
 bucket VARCHAR(100) NOT NULL,key_hash CHAR(64) NOT NULL,window_started_at DATETIME NOT NULL,hits INT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(bucket,key_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,event_uuid CHAR(36) NOT NULL UNIQUE,occurred_at DATETIME(6) NOT NULL,actor_user_id BIGINT UNSIGNED NULL,event_type VARCHAR(120) NOT NULL,entity_type VARCHAR(100) NOT NULL,entity_id VARCHAR(120) NULL,request_id VARCHAR(120) NULL,ip_hash CHAR(64) NULL,user_agent_hash CHAR(64) NULL,metadata_json JSON NOT NULL,prev_hash CHAR(64) NULL,event_hash CHAR(64) NOT NULL UNIQUE,
 INDEX idx_audit_entity(entity_type,entity_id,occurred_at),INDEX idx_audit_actor(actor_user_id,occurred_at),FOREIGN KEY(actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS update_runs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,reference VARCHAR(100) NOT NULL UNIQUE,from_version VARCHAR(40) NULL,to_version VARCHAR(40) NOT NULL,status ENUM('prepared','running','completed','failed','rolled_back') NOT NULL,started_by BIGINT UNSIGNED NULL,started_at DATETIME NULL,completed_at DATETIME NULL,heartbeat_at DATETIME NULL,preflight_json JSON NULL,error_code VARCHAR(120) NULL,FOREIGN KEY(started_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS update_steps (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,update_run_id BIGINT UNSIGNED NOT NULL,step_no INT UNSIGNED NOT NULL,name VARCHAR(190) NOT NULL,status ENUM('pending','running','completed','failed','skipped') NOT NULL,started_at DATETIME NULL,completed_at DATETIME NULL,message TEXT NULL,UNIQUE KEY uq_update_step(update_run_id,step_no),FOREIGN KEY(update_run_id) REFERENCES update_runs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles(code,name,system_role,active) VALUES
('super_admin_technical','Super Administrador Técnico',1,1),('commercial_director','Director Comercial',1,1),('commercial_manager','Gestor/Supervisor Comercial',1,1),('commercial','Comercial',1,1),('marketing','Marketing',1,1),('executive_dashboard','Administração/Executivo',1,1),('audit_readonly','Consulta/Auditoria',1,1)
ON DUPLICATE KEY UPDATE name=VALUES(name),active=1;
INSERT INTO permissions(code,description) VALUES
('crm.leads.view','Consultar leads/contactos/organizações conforme âmbito'),('crm.leads.manage','Criar e gerir leads/contactos/organizações'),('crm.opportunities.view','Consultar oportunidades conforme âmbito'),('crm.opportunities.manage','Criar e gerir oportunidades'),('crm.pipeline.manage','Gerir pipeline e mudanças de fase'),('crm.activities.manage','Gerir actividades, tarefas e follow-ups'),('crm.assignments.manage','Gerir atribuições de responsáveis/equipas/carteiras'),('crm.campaigns.manage','Gerir campanhas comerciais'),('crm.forecast.view','Consultar cockpit e previsão comercial'),('crm.reports.view','Consultar relatórios'),('crm.reports.export','Exportar relatórios autorizados'),('crm.settings.manage','Gerir parametrizações e Centro de Actualizações'),('crm.integrations.manage','Gerir integrações comerciais'),('crm.audit.view','Consultar auditoria')
ON DUPLICATE KEY UPDATE description=VALUES(description);
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='super_admin_technical';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.leads.view','crm.leads.manage','crm.opportunities.view','crm.opportunities.manage','crm.pipeline.manage','crm.activities.manage','crm.assignments.manage','crm.campaigns.manage','crm.forecast.view','crm.reports.view','crm.reports.export','crm.audit.view') WHERE r.code='commercial_director';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.leads.view','crm.leads.manage','crm.opportunities.view','crm.opportunities.manage','crm.pipeline.manage','crm.activities.manage','crm.assignments.manage','crm.forecast.view','crm.reports.view') WHERE r.code='commercial_manager';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.leads.view','crm.leads.manage','crm.opportunities.view','crm.opportunities.manage','crm.activities.manage') WHERE r.code='commercial';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.leads.view','crm.campaigns.manage','crm.reports.view') WHERE r.code='marketing';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.forecast.view','crm.reports.view') WHERE r.code='executive_dashboard';
INSERT IGNORE INTO role_permissions(role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p ON p.code IN('crm.leads.view','crm.opportunities.view','crm.reports.view','crm.audit.view') WHERE r.code='audit_readonly';

INSERT INTO lead_sources(code,name,active) VALUES ('ambassador','SUPER Embaixador',1),('website','Website',1),('referral','Referência/Indicação',1),('campaign','Campanha',1),('direct','Contacto directo',1),('other','Outra origem',1) ON DUPLICATE KEY UPDATE name=VALUES(name),active=1;
INSERT INTO lead_state_definitions(code,name,canonical_status,position,active,immutable_semantic_key) VALUES
('new','Lead','open',10,1,'lead'),('qualification','Qualificação','open',20,1,'qualification'),('contact','Contacto','open',30,1,'contact'),('needs','Levantamento de necessidade','open',40,1,'needs_assessment'),('quote','Cotação','open',50,1,'quote'),('negotiation','Negociação','open',60,1,'negotiation'),('converted','Ganho/Convertido','converted',70,1,'won'),('lost','Perdido/Não convertido','lost',80,1,'lost')
ON DUPLICATE KEY UPDATE name=VALUES(name),canonical_status=VALUES(canonical_status),position=VALUES(position),active=1;
INSERT INTO pipeline_stages(code,name,semantic_key,canonical_status,position,default_probability,active) VALUES
('qualification','Qualificação','qualification','open',10,10,1),('contact','Contacto','contact','open',20,20,1),('needs','Levantamento de necessidade','needs_assessment','open',30,35,1),('quote','Cotação','quote','open',40,50,1),('negotiation','Negociação','negotiation','open',50,70,1),('won','Ganho','won','won',60,100,1),('lost','Perdido','lost','lost',70,0,1)
ON DUPLICATE KEY UPDATE name=VALUES(name),canonical_status=VALUES(canonical_status),position=VALUES(position),default_probability=VALUES(default_probability),active=1;
INSERT INTO sla_policies(code,name,entity_type,trigger_event,target_minutes,warning_before_minutes,escalation_role_code,priority,explanation,active,created_at,updated_at)
VALUES('lead-first-contact','Primeiro contacto do novo lead','lead','lead.created',240,60,'commercial_manager',10,'Prazo-base inicial parametrizável. Deve ser ajustado pelo Director Comercial antes da entrada em produção.',1,UTC_TIMESTAMP(),UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE name=VALUES(name),target_minutes=VALUES(target_minutes),warning_before_minutes=VALUES(warning_before_minutes),active=1,updated_at=UTC_TIMESTAMP();

DROP TRIGGER IF EXISTS trg_audit_no_update;
DROP TRIGGER IF EXISTS trg_audit_no_delete;
CREATE TRIGGER trg_audit_no_update BEFORE UPDATE ON audit_events FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='audit_events is append-only';
CREATE TRIGGER trg_audit_no_delete BEFORE DELETE ON audit_events FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='audit_events is append-only';