-- QRIS Soundbox Cloud v1.0
-- Import via phpMyAdmin atau: mysql -u user -p database < schema.sql

CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(64) NOT NULL UNIQUE,
    amount INT NOT NULL,
    payer_name VARCHAR(128) DEFAULT NULL,
    payer_bank VARCHAR(64) DEFAULT NULL,
    payment_method VARCHAR(32) NOT NULL DEFAULT 'qris',
    status ENUM('pending','paid','expired','failed') NOT NULL DEFAULT 'pending',
    external_ref VARCHAR(128) DEFAULT NULL,
    note VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    INDEX idx_paid (paid_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS merchant_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_type VARCHAR(32) NOT NULL DEFAULT 'payment_success',
    ref_id VARCHAR(64) DEFAULT NULL,
    amount INT NOT NULL DEFAULT 0,
    message VARCHAR(255) DEFAULT NULL,
    payer_info VARCHAR(255) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_id (id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS qris_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(64) NOT NULL UNIQUE,
    amount INT NOT NULL,
    qr_url TEXT DEFAULT NULL,
    checkout_url TEXT DEFAULT NULL,
    status ENUM('pending','paid','expired') NOT NULL DEFAULT 'pending',
    expires_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS soundbox_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_name VARCHAR(64) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    mode ENUM('kasir','monitor','owner') NOT NULL DEFAULT 'kasir',
    sound_enabled TINYINT NOT NULL DEFAULT 1,
    voice_enabled TINYINT NOT NULL DEFAULT 1,
    play_missed TINYINT NOT NULL DEFAULT 1,
    last_event_id INT NOT NULL DEFAULT 0,
    last_seen_at DATETIME DEFAULT NULL,
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token (token),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS wa_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('sent','failed','skipped') NOT NULL DEFAULT 'sent',
    response TEXT DEFAULT NULL,
    ref_id VARCHAR(64) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS expected_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    expected_amount INT NOT NULL,
    status ENUM('waiting','matched','mismatch','cancelled','expired') NOT NULL DEFAULT 'waiting',
    matched_ref_id VARCHAR(64) DEFAULT NULL,
    matched_amount INT DEFAULT NULL,
    note VARCHAR(255) DEFAULT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS daily_recap_log (
    recap_date DATE NOT NULL PRIMARY KEY,
    sent_at DATETIME NOT NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'sent',
    message_preview VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_registrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    store_name VARCHAR(128) NOT NULL,
    owner_name VARCHAR(128) NOT NULL,
    owner_phone VARCHAR(20) NOT NULL,
    owner_email VARCHAR(128) DEFAULT NULL,
    city VARCHAR(64) DEFAULT NULL,
    package ENUM('basic','pro','pro_plus') NOT NULL DEFAULT 'pro',
    status ENUM('pending','active','rejected') NOT NULL DEFAULT 'pending',
    billing_status ENUM('trial','active','past_due','suspended') NOT NULL DEFAULT 'trial',
    trial_ends_at DATETIME DEFAULT NULL,
    last_paid_period VARCHAR(7) DEFAULT NULL,
    soundbox_token VARCHAR(64) DEFAULT NULL,
    admin_note TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscription_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    registration_id INT NOT NULL,
    period VARCHAR(7) NOT NULL COMMENT 'YYYY-MM',
    ref_id VARCHAR(64) NOT NULL UNIQUE,
    amount INT NOT NULL,
    package VARCHAR(16) NOT NULL,
    status ENUM('pending','paid','expired','cancelled') NOT NULL DEFAULT 'pending',
    payment_url TEXT DEFAULT NULL,
    qr_url TEXT DEFAULT NULL,
    due_at DATETIME NOT NULL,
    paid_at DATETIME DEFAULT NULL,
    reminder_count INT NOT NULL DEFAULT 0,
    last_reminder_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_reg_period (registration_id, period),
    INDEX idx_status (status),
    INDEX idx_due (due_at),
    INDEX idx_registration (registration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Untuk versi jual (multi-toko) nanti — v1 pakai config.php saja
CREATE TABLE IF NOT EXISTS tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(32) NOT NULL UNIQUE,
    store_name VARCHAR(128) NOT NULL,
    owner_phone VARCHAR(20) DEFAULT NULL,
    soundbox_token VARCHAR(64) NOT NULL,
    tripay_enabled TINYINT NOT NULL DEFAULT 0,
    tripay_api_key VARCHAR(255) DEFAULT NULL,
    tripay_private_key VARCHAR(255) DEFAULT NULL,
    tripay_merchant_code VARCHAR(32) DEFAULT NULL,
    tripay_mode ENUM('sandbox','production') NOT NULL DEFAULT 'sandbox',
    status TINYINT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;