CREATE TABLE IF NOT EXISTS roles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS regions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS teams (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, region_id BIGINT UNSIGNED NULL, active TINYINT(1) DEFAULT 1, FOREIGN KEY(region_id) REFERENCES regions(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, public_id VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, username VARCHAR(80) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role_id BIGINT UNSIGNED NOT NULL, region_id BIGINT UNSIGNED NULL, team_id BIGINT UNSIGNED NULL, phone VARCHAR(40), email VARCHAR(150), telegram_id VARCHAR(100), avatar VARCHAR(255), status ENUM('ACTIVE','INACTIVE') DEFAULT 'ACTIVE', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(region_id) REFERENCES regions(id), FOREIGN KEY(team_id) REFERENCES teams(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS auth_sessions (token_hash CHAR(64) PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, expires_at DATETIME NOT NULL, last_activity_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX(expires_at), INDEX idx_auth_sessions_user_expiry(user_id,expires_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ticket_priorities (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(30) NOT NULL UNIQUE, name VARCHAR(80) NOT NULL, sla_hours INT NULL, color VARCHAR(20) NOT NULL, level INT NOT NULL, active TINYINT(1) DEFAULT 1) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS tickets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_number VARCHAR(80) NOT NULL UNIQUE, category VARCHAR(120) NOT NULL, customer_mode ENUM('MASTER','MANUAL') DEFAULT 'MASTER', customer_name VARCHAR(150) NOT NULL, priority_id BIGINT UNSIGNED NULL, status_code VARCHAR(60) NOT NULL DEFAULT 'OPEN', region_id BIGINT UNSIGNED NULL, team_id BIGINT UNSIGNED NULL, pic_user_id BIGINT UNSIGNED NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(priority_id) REFERENCES ticket_priorities(id), FOREIGN KEY(region_id) REFERENCES regions(id), FOREIGN KEY(team_id) REFERENCES teams(id), FOREIGN KEY(pic_user_id) REFERENCES users(id), FOREIGN KEY(created_by) REFERENCES users(id), INDEX(status_code), INDEX idx_tickets_scope_status(region_id,team_id,pic_user_id,status_code)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ticket_assignments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, ticket_id BIGINT UNSIGNED NOT NULL, team_id BIGINT UNSIGNED NULL, pic_user_id BIGINT UNSIGNED NULL, assigned_by BIGINT UNSIGNED NOT NULL, reason VARCHAR(255), active TINYINT(1) DEFAULT 1, assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ended_at DATETIME NULL, FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE, FOREIGN KEY(team_id) REFERENCES teams(id), FOREIGN KEY(pic_user_id) REFERENCES users(id), FOREIGN KEY(assigned_by) REFERENCES users(id), INDEX idx_ticket_assignments_active(ticket_id,active,assigned_at)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS work_trips (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, trip_number VARCHAR(80) NOT NULL UNIQUE, trip_type ENUM('TICKET','ACTIVITY') NOT NULL DEFAULT 'TICKET', assignment_type ENUM('TEAM','PIC') NOT NULL DEFAULT 'TEAM', region_id BIGINT UNSIGNED NULL, team_id BIGINT UNSIGNED NULL, agenda VARCHAR(180) NULL, destination_name VARCHAR(180) NULL, destination_address VARCHAR(255) NULL, destination_latitude DECIMAL(10,7) NULL, destination_longitude DECIMAL(10,7) NULL, scheduled_at DATETIME NULL, notes VARCHAR(500) NULL, vehicle_name VARCHAR(120), vehicle_type ENUM('MOTOR','MOBIL') DEFAULT 'MOTOR', distance_km DECIMAL(12,3) DEFAULT 0, duration_minutes INT DEFAULT 0, route_version INT DEFAULT 1, status VARCHAR(40) DEFAULT 'PLANNED', started_at DATETIME NULL, started_by BIGINT UNSIGNED NULL, completed_at DATETIME NULL, completed_by BIGINT UNSIGNED NULL, cancelled_at DATETIME NULL, cancelled_by BIGINT UNSIGNED NULL, cancel_reason VARCHAR(500) NULL, created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(region_id) REFERENCES regions(id), FOREIGN KEY(team_id) REFERENCES teams(id), FOREIGN KEY(created_by) REFERENCES users(id)) ENGINE=InnoDB;

-- Rev_9: OPS Tambahan table is created in the base schema as well as the additive migrator.
-- This prevents a partially completed legacy migration from leaving this required table absent.
CREATE TABLE IF NOT EXISTS work_trip_ops_adjustments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trip_id BIGINT UNSIGNED NOT NULL,
 route_version INT UNSIGNED NOT NULL,
 baseline_approved_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 new_estimated_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 delta_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 approved_amount DECIMAL(14,2) NULL,
 status ENUM('DRAFT','WAITING_FINANCE','APPROVED','REJECTED','SUPERSEDED','CANCELLED') NOT NULL DEFAULT 'DRAFT',
 created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_trip_delta_version(trip_id,route_version),
 INDEX idx_trip_delta_status(trip_id,status),
 FOREIGN KEY(trip_id) REFERENCES work_trips(id) ON DELETE CASCADE,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Rev_10 OPS & Finance: immutable disbursement ledger (also ensured by additive migrator).
CREATE TABLE IF NOT EXISTS ops_disbursements (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 trip_id BIGINT UNSIGNED NOT NULL,
 source_type ENUM('BASE','ADDITIONAL') NOT NULL,
 source_id BIGINT UNSIGNED NOT NULL,
 amount DECIMAL(14,2) NOT NULL,
 payment_method ENUM('TRANSFER','CASH','OTHER') NOT NULL,
 payment_reference VARCHAR(120) NOT NULL,
 note VARCHAR(500) NULL,
 paid_by BIGINT UNSIGNED NOT NULL,
 paid_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_ops_disbursement_source(source_type,source_id),
 INDEX idx_ops_disbursement_trip(trip_id,paid_at),
 FOREIGN KEY(trip_id) REFERENCES work_trips(id) ON DELETE RESTRICT,
 FOREIGN KEY(paid_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS work_trip_tickets (trip_id BIGINT UNSIGNED NOT NULL, ticket_id BIGINT UNSIGNED NOT NULL, stop_order INT DEFAULT 1, PRIMARY KEY(trip_id,ticket_id), FOREIGN KEY(trip_id) REFERENCES work_trips(id) ON DELETE CASCADE, FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE, INDEX idx_work_trip_tickets_ticket(ticket_id,trip_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS work_trip_participants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, trip_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, participant_role ENUM('PIC','MEMBER') NOT NULL DEFAULT 'MEMBER', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_work_trip_participant(trip_id,user_id), FOREIGN KEY(trip_id) REFERENCES work_trips(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE RESTRICT) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS app_settings (`key` VARCHAR(100) PRIMARY KEY, value_json JSON NOT NULL, updated_by BIGINT UNSIGNED NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(updated_by) REFERENCES users(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, actor_user_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(80) NOT NULL, entity_id VARCHAR(100), detail_json JSON NULL, ip VARCHAR(64), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(actor_user_id) REFERENCES users(id), INDEX(entity_type,entity_id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS soft_deleted_records (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,entity_type VARCHAR(80) NOT NULL,entity_id VARCHAR(120) NOT NULL,display_label VARCHAR(255) NOT NULL,snapshot_json JSON NOT NULL,deleted_by BIGINT UNSIGNED NOT NULL,deleted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE KEY uq_soft_deleted_entity(entity_type,entity_id),INDEX idx_soft_deleted_at(deleted_at),FOREIGN KEY(deleted_by) REFERENCES users(id) ON DELETE RESTRICT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(100) NOT NULL UNIQUE, module VARCHAR(60) NOT NULL, action VARCHAR(60) NOT NULL, label VARCHAR(120) NOT NULL) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS role_permissions (role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, scope ENUM('ALL','REGION','TEAM','ASSIGNED','DOMAIN') NOT NULL DEFAULT 'DOMAIN', PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS role_permission_scope_configs (role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, combine_mode ENUM('AND','OR') NOT NULL DEFAULT 'OR', region_ids_json JSON NULL, team_ids_json JSON NULL, user_ids_json JSON NULL, object_types_json JSON NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY(role_id,permission_id), FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE, FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS internet_packages (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, download_mbps INT NOT NULL DEFAULT 0, upload_mbps INT NOT NULL DEFAULT 0, monthly_price DECIMAL(14,2) NOT NULL DEFAULT 0, installation_fee DECIMAL(14,2) NOT NULL DEFAULT 0, package_type VARCHAR(60) DEFAULT 'REGULER', billing_cycle VARCHAR(60) DEFAULT 'BULANAN', fup_gb INT NULL, description TEXT NULL, active TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS service_statuses (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, description VARCHAR(255), color VARCHAR(20) DEFAULT '#64748b', active TINYINT(1) DEFAULT 1) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS vehicles (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, vehicle_type ENUM('MOTOR','MOBIL') NOT NULL, license_plate VARCHAR(30), region_id BIGINT UNSIGNED NULL, status ENUM('ACTIVE','INACTIVE','MAINTENANCE') DEFAULT 'ACTIVE', notes VARCHAR(255), FOREIGN KEY(region_id) REFERENCES regions(id)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ticket_categories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, prefix VARCHAR(20) NOT NULL, active TINYINT(1) DEFAULT 1, UNIQUE KEY uq_ticket_category_prefix(prefix)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ticket_subcategories (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id BIGINT UNSIGNED NOT NULL, code VARCHAR(40) NOT NULL UNIQUE, name VARCHAR(120) NOT NULL, prefix VARCHAR(20) NOT NULL, active TINYINT(1) DEFAULT 1, UNIQUE KEY uq_ticket_subcategory_prefix(category_id,prefix), FOREIGN KEY(category_id) REFERENCES ticket_categories(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS ticket_number_sequences (sequence_key VARCHAR(80) PRIMARY KEY, last_number INT UNSIGNED NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP) ENGINE=InnoDB;

-- Milestone 3: Data Pelanggan + Network
CREATE TABLE IF NOT EXISTS customer_number_sequences (`year_month` CHAR(6) PRIMARY KEY, last_number INT NOT NULL DEFAULT 0) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, customer_id VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL,
 phone VARCHAR(40), email VARCHAR(150), address VARCHAR(255), region_id BIGINT UNSIGNED NULL, package_id BIGINT UNSIGNED NULL,
 service_status_id BIGINT UNSIGNED NULL, activation_date DATE NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
 pop_id BIGINT UNSIGNED NULL, olt_id BIGINT UNSIGNED NULL, pon_id BIGINT UNSIGNED NULL, odc_id BIGINT UNSIGNED NULL, odp_id BIGINT UNSIGNED NULL, port_id BIGINT UNSIGNED NULL,
 ont_serial VARCHAR(100), router_serial VARCHAR(100), active TINYINT(1) DEFAULT 1, created_by BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(region_id) REFERENCES regions(id), FOREIGN KEY(package_id) REFERENCES internet_packages(id), FOREIGN KEY(service_status_id) REFERENCES service_statuses(id), FOREIGN KEY(created_by) REFERENCES users(id), INDEX(name), INDEX(phone)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS network_nodes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, node_type ENUM('POP','OLT','PON','ODC','ODP') NOT NULL, code VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL,
 region_id BIGINT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED NULL, address VARCHAR(255), latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL,
 total_ports INT NULL, status ENUM('ACTIVE','INACTIVE','MAINTENANCE') DEFAULT 'ACTIVE', notes VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(region_id) REFERENCES regions(id), FOREIGN KEY(parent_id) REFERENCES network_nodes(id), INDEX(node_type), INDEX(parent_id)
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS network_ports (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, odp_id BIGINT UNSIGNED NOT NULL, port_number INT NOT NULL, label VARCHAR(60), status ENUM('AVAILABLE','USED','RESERVED','DAMAGED') DEFAULT 'AVAILABLE', customer_id BIGINT UNSIGNED NULL,
 UNIQUE KEY uq_odp_port(odp_id,port_number), FOREIGN KEY(odp_id) REFERENCES network_nodes(id) ON DELETE CASCADE, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Milestone 6 tables are created/extended idempotently by database/migrate.js.

CREATE TABLE IF NOT EXISTS hard_delete_audit (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 actor_user_id BIGINT UNSIGNED NULL,
 entity_type VARCHAR(80) NOT NULL,
 entity_id VARCHAR(120) NOT NULL,
 reason VARCHAR(500) NOT NULL,
 snapshot_json JSON NULL,
 ip VARCHAR(64) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(actor_user_id) REFERENCES users(id) ON DELETE SET NULL,
 INDEX(entity_type,entity_id), INDEX(created_at)
) ENGINE=InnoDB;
