-- ============================================================
-- GASFLOW SaaS v2.0 — Multi-Tenant Database Schema
-- Upgraded: Activity audit, email verification, password reset,
-- platform settings, debtor tracking, PIN change support
-- ============================================================


-- ── PLATFORM SETTINGS (Super Admin configurable) ────────────
CREATE TABLE IF NOT EXISTS `platform_settings` (
    `key`             VARCHAR(100)    PRIMARY KEY,
    `value`           TEXT            NOT NULL,
    `label`           VARCHAR(200)    DEFAULT NULL,
    `group`           VARCHAR(50)     NOT NULL DEFAULT 'general',
    `updated_by`      INT UNSIGNED    DEFAULT NULL,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ── SHOPS (Tenants) ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `shops` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid`            CHAR(36)        NOT NULL UNIQUE,
    `name`            VARCHAR(150)    NOT NULL,
    `slug`            VARCHAR(160)    NOT NULL UNIQUE,
    `email`           VARCHAR(255)    NOT NULL UNIQUE,
    `phone`           VARCHAR(20)     DEFAULT NULL,
    `address`         TEXT            DEFAULT NULL,
    `price_per_kg`    DECIMAL(10,2)   NOT NULL DEFAULT 1400.00,
    `logo_url`        VARCHAR(500)    DEFAULT NULL,
    `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
    `subscription_status` ENUM('active','expired','trial','cancelled') NOT NULL DEFAULT 'trial',
    `trial_ends_at`   DATETIME        DEFAULT NULL,
    `timezone`        VARCHAR(50)     NOT NULL DEFAULT 'Africa/Lagos',
    `settings`        JSON            DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_shops_active` (`is_active`, `subscription_status`),
    INDEX `idx_shops_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── ACTIVITY LOG (Audit Trail for EVERYONE) ─────────────────
CREATE TABLE IF NOT EXISTS `activity_log` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    DEFAULT NULL,
    `user_id`         INT UNSIGNED    DEFAULT NULL,
    `user_name`       VARCHAR(150)    DEFAULT NULL,
    `user_role`       VARCHAR(20)     DEFAULT NULL,
    `action`          VARCHAR(100)    NOT NULL,
    `entity_type`     VARCHAR(50)     DEFAULT NULL,
    `entity_id`       INT UNSIGNED    DEFAULT NULL,
    `description`     TEXT            DEFAULT NULL,
    `metadata`        JSON            DEFAULT NULL,
    `ip_address`      VARCHAR(45)     DEFAULT NULL,
    `user_agent`      VARCHAR(500)    DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_activity_shop` (`shop_id`, `created_at`),
    INDEX `idx_activity_user` (`user_id`, `created_at`),
    INDEX `idx_activity_action` (`action`, `created_at`),
    INDEX `idx_activity_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── TRUCKS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `trucks` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid`            CHAR(36)        NOT NULL UNIQUE,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `name`            VARCHAR(100)    NOT NULL,
    `plate_number`    VARCHAR(20)     DEFAULT NULL,
    `quantity_bought_kg` DECIMAL(12,2) NOT NULL DEFAULT 0,
    `quantity_sold_kg`   DECIMAL(12,2) NOT NULL DEFAULT 0,
    `purchase_price_per_kg` DECIMAL(10,2) NOT NULL DEFAULT 0,
    `selling_price_per_kg`  DECIMAL(10,2) NOT NULL DEFAULT 0,
    `status`          ENUM('active','depleted','inactive') NOT NULL DEFAULT 'active',
    `low_stock_threshold_kg` DECIMAL(10,2) NOT NULL DEFAULT 100.00,
    `notes`           TEXT            DEFAULT NULL,
    `received_at`     DATETIME        DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_trucks_shop` (`shop_id`, `status`),
    CONSTRAINT `fk_trucks_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SALES ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sales` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `uuid`            CHAR(36)        NOT NULL UNIQUE,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `user_id`         INT UNSIGNED    NOT NULL,
    `truck_id`        INT UNSIGNED    DEFAULT NULL,
    `client_tx_id`    VARCHAR(64)     NOT NULL,
    `quantity_kg`     DECIMAL(10,2)   NOT NULL,
    `price_per_kg`    DECIMAL(10,2)   NOT NULL,
    `total_amount`    DECIMAL(12,2)   NOT NULL,
    `payment_method`  ENUM('cash','transfer','pos','credit') NOT NULL DEFAULT 'cash',
    `customer_name`   VARCHAR(150)    DEFAULT NULL,
    `customer_phone`  VARCHAR(20)     DEFAULT NULL,
    `receipt_number`  VARCHAR(30)     NOT NULL,
    `notes`           TEXT            DEFAULT NULL,
    `is_credit`       TINYINT(1)      NOT NULL DEFAULT 0,
    `credit_paid`     TINYINT(1)      NOT NULL DEFAULT 0,
    `credit_paid_at`  DATETIME        DEFAULT NULL,
    `is_synced`       TINYINT(1)      NOT NULL DEFAULT 1,
    `sold_at`         DATETIME        NOT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_client_tx` (`shop_id`, `client_tx_id`),
    INDEX `idx_sales_shop_date` (`shop_id`, `sold_at`),
    INDEX `idx_sales_user` (`user_id`, `sold_at`),
    INDEX `idx_sales_truck` (`truck_id`),
    INDEX `idx_sales_receipt` (`receipt_number`),
    INDEX `idx_sales_credit` (`shop_id`, `is_credit`, `credit_paid`),
    CONSTRAINT `fk_sales_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sales_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sales_truck` FOREIGN KEY (`truck_id`) REFERENCES `trucks`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── QUICK PRESETS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `quick_presets` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `label`           VARCHAR(30)     NOT NULL,
    `quantity_kg`     DECIMAL(10,2)   NOT NULL,
    `sort_order`      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `is_active`       TINYINT(1)      NOT NULL DEFAULT 1,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_presets_shop` (`shop_id`, `is_active`, `sort_order`),
    CONSTRAINT `fk_presets_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SYNC LOGS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `sync_logs` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `user_id`         INT UNSIGNED    NOT NULL,
    `batch_id`        VARCHAR(64)     NOT NULL,
    `records_pushed`  INT UNSIGNED    NOT NULL DEFAULT 0,
    `records_accepted` INT UNSIGNED   NOT NULL DEFAULT 0,
    `records_rejected` INT UNSIGNED   NOT NULL DEFAULT 0,
    `errors`          JSON            DEFAULT NULL,
    `device_info`     VARCHAR(255)    DEFAULT NULL,
    `synced_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_sync_shop` (`shop_id`, `synced_at`),
    CONSTRAINT `fk_sync_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sync_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SUBSCRIPTIONS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `subscriptions` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `paystack_customer_code` VARCHAR(100) DEFAULT NULL,
    `paystack_subscription_code` VARCHAR(100) DEFAULT NULL,
    `paystack_plan_code` VARCHAR(100)  DEFAULT NULL,
    `amount`          DECIMAL(10,2)   NOT NULL DEFAULT 3000.00,
    `currency`        CHAR(3)         NOT NULL DEFAULT 'NGN',
    `status`          ENUM('active','expired','cancelled','pending') NOT NULL DEFAULT 'pending',
    `current_period_start` DATETIME   DEFAULT NULL,
    `current_period_end`   DATETIME   DEFAULT NULL,
    `last_payment_at` DATETIME        DEFAULT NULL,
    `next_charge_at`  DATETIME        DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_sub_shop` (`shop_id`, `status`),
    INDEX `idx_sub_paystack` (`paystack_subscription_code`),
    CONSTRAINT `fk_sub_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PAYMENT LOGS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `payment_logs` (
    `id`              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `subscription_id` INT UNSIGNED    DEFAULT NULL,
    `paystack_reference` VARCHAR(100) NOT NULL UNIQUE,
    `amount`          DECIMAL(10,2)   NOT NULL,
    `currency`        CHAR(3)         NOT NULL DEFAULT 'NGN',
    `status`          ENUM('success','failed','pending','reversed') NOT NULL,
    `channel`         VARCHAR(30)     DEFAULT NULL,
    `payload`         JSON            DEFAULT NULL,
    `paid_at`         DATETIME        DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_pay_shop` (`shop_id`, `paid_at`),
    CONSTRAINT `fk_pay_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_pay_sub` FOREIGN KEY (`subscription_id`) REFERENCES `subscriptions`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDIT REPORTS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `audit_reports` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `report_type`     ENUM('daily','weekly','monthly','yearly') NOT NULL,
    `period_start`    DATE            NOT NULL,
    `period_end`      DATE            NOT NULL,
    `total_volume_bought_kg` DECIMAL(14,2) NOT NULL DEFAULT 0,
    `total_volume_sold_kg`   DECIMAL(14,2) NOT NULL DEFAULT 0,
    `total_revenue`   DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `total_cost`      DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `gross_margin`    DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `total_transactions` INT UNSIGNED NOT NULL DEFAULT 0,
    `data`            JSON            DEFAULT NULL,
    `generated_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_report` (`shop_id`, `report_type`, `period_start`),
    CONSTRAINT `fk_report_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── CUSTOMERS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `customers` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `shop_id`         INT UNSIGNED    NOT NULL,
    `name`            VARCHAR(150)    NOT NULL,
    `phone`           VARCHAR(20)     DEFAULT NULL,
    `total_purchases` INT UNSIGNED    NOT NULL DEFAULT 0,
    `total_spent`     DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `total_credit`    DECIMAL(14,2)   NOT NULL DEFAULT 0,
    `total_credit_paid` DECIMAL(14,2) NOT NULL DEFAULT 0,
    `last_purchase_at` DATETIME       DEFAULT NULL,
    `created_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_cust_shop` (`shop_id`),
    INDEX `idx_cust_phone` (`shop_id`, `phone`),
    INDEX `idx_cust_credit` (`shop_id`, `total_credit`),
    CONSTRAINT `fk_cust_shop` FOREIGN KEY (`shop_id`) REFERENCES `shops`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

