-- KINET MikroTik Admin Module - Update v17 Infrastructure Fiber + Customer ODP/Map
-- Jalankan sekali setelah replace file v17.

CREATE TABLE IF NOT EXISTS olt_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    brand VARCHAR(120) NULL,
    model VARCHAR(120) NULL,
    ip_address VARCHAR(80) NULL,
    location VARCHAR(255) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    pon_ports INT NOT NULL DEFAULT 0,
    status ENUM('online','offline','maintenance','inactive') NOT NULL DEFAULT 'online',
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_olt_status (status),
    INDEX idx_olt_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS odc_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    olt_id INT NULL,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    total_splitter INT NOT NULL DEFAULT 0,
    status ENUM('active','full','maintenance','inactive') NOT NULL DEFAULT 'active',
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_odc_olt (olt_id),
    INDEX idx_odc_status (status),
    INDEX idx_odc_code (code),
    CONSTRAINT fk_odc_olt FOREIGN KEY (olt_id) REFERENCES olt_devices(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS odp_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    odc_id INT NULL,
    code VARCHAR(80) NOT NULL UNIQUE,
    name VARCHAR(160) NOT NULL,
    address TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    total_ports INT NOT NULL DEFAULT 8,
    used_ports INT NOT NULL DEFAULT 0,
    status ENUM('active','full','maintenance','inactive') NOT NULL DEFAULT 'active',
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_odp_odc (odc_id),
    INDEX idx_odp_status (status),
    INDEX idx_odp_code (code),
    CONSTRAINT fk_odp_odc FOREIGN KEY (odc_id) REFERENCES odc_devices(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO olt_devices (code, name, brand, location, status, note)
SELECT 'DEFAULT-OLT', 'Default OLT', 'Default', 'Default', 'online', 'Data default. Ubah dari menu Infrastruktur Fiber > OLT.'
WHERE NOT EXISTS (SELECT 1 FROM olt_devices WHERE code='DEFAULT-OLT');

INSERT INTO odc_devices (olt_id, code, name, address, status, note)
SELECT (SELECT id FROM olt_devices WHERE code='DEFAULT-OLT' LIMIT 1), 'DEFAULT-ODC', 'Default ODC', 'Default', 'active', 'Data default. Ubah dari menu Infrastruktur Fiber > ODC.'
WHERE NOT EXISTS (SELECT 1 FROM odc_devices WHERE code='DEFAULT-ODC');

INSERT INTO odp_devices (odc_id, code, name, address, total_ports, used_ports, status, note)
SELECT (SELECT id FROM odc_devices WHERE code='DEFAULT-ODC' LIMIT 1), 'DEFAULT-ODP', 'Default ODP', 'Default', 8, 0, 'active', 'Data default jika ODP belum dibuat.'
WHERE NOT EXISTS (SELECT 1 FROM odp_devices WHERE code='DEFAULT-ODP');

ALTER TABLE customers ADD COLUMN IF NOT EXISTS odp_id INT NULL AFTER subscription_profile_id;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS installation_latitude DECIMAL(10,7) NULL AFTER address;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS installation_longitude DECIMAL(10,7) NULL AFTER installation_latitude;
ALTER TABLE customers ADD INDEX IF NOT EXISTS idx_customers_odp_id (odp_id);

UPDATE customers SET odp_id = (SELECT id FROM odp_devices WHERE code='DEFAULT-ODP' LIMIT 1) WHERE odp_id IS NULL;

CREATE TABLE IF NOT EXISTS customer_tickets (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ticket_no VARCHAR(80) NOT NULL UNIQUE,
    customer_id INT NULL,
    category VARCHAR(100) NOT NULL DEFAULT 'Keluhan',
    title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    status ENUM('open','process','pending','resolved','closed') NOT NULL DEFAULT 'open',
    assigned_to INT NULL,
    opened_by INT NULL,
    resolved_at DATETIME NULL,
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ticket_customer (customer_id),
    INDEX idx_ticket_status (status),
    INDEX idx_ticket_assigned (assigned_to),
    CONSTRAINT fk_ticket_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ticket_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_ticket_opened FOREIGN KEY (opened_by) REFERENCES users(id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;