-- Ots-botWhats — Full multi-tenant schema
CREATE DATABASE IF NOT EXISTS ots_botwhats CHARACTER SET utf8mb4;
USE ots_botwhats;

-- Admin Mkuu (wamiliki wa mfumo)
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Plans (Basic / Pro / Enterprise)
CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price_monthly DECIMAL(10,2) NOT NULL,
    max_auto_replies INT DEFAULT 20,
    ai_enabled TINYINT(1) DEFAULT 0
);

INSERT INTO plans (name, price_monthly, max_auto_replies, ai_enabled) VALUES
('Basic', 20000, 10, 0),
('Pro', 45000, 50, 0),
('Enterprise', 90000, 999, 1);

-- Wateja (Customers / Merchants — mfano Mama Ntilie)
CREATE TABLE merchants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(150) NOT NULL,
    business_type VARCHAR(50), -- Mkahawa, Saluni, Duka...
    owner_phone VARCHAR(20),
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    plan_id INT DEFAULT 1,

    -- WhatsApp connection (Njia B: manual) au (Njia A: embedded, baadaye)
    whatsapp_phone_number VARCHAR(20),
    whatsapp_phone_number_id VARCHAR(100),
    whatsapp_access_token TEXT,
    whatsapp_connected TINYINT(1) DEFAULT 0,

    status ENUM('active','suspended','expired') DEFAULT 'active',
    subscription_expiry DATE,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

-- Mazungumzo yote (per merchant)
CREATE TABLE conversations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    customer_name VARCHAR(100),
    direction ENUM('incoming','outgoing') NOT NULL,
    message_text TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Session ya kila mazungumzo (kwa order flow za hatua nyingi - baadaye)
CREATE TABLE sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    customer_phone VARCHAR(20) NOT NULL,
    current_step VARCHAR(50) DEFAULT 'start',
    data JSON,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_merchant_phone (merchant_id, customer_phone),
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Majibu ya kiotomatiki ya kila merchant
CREATE TABLE auto_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    reply_text TEXT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Maktaba ya Templates (Admin Mkuu anasimamia)
CREATE TABLE templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,       -- mfano: "Mkahawa", "Saluni"
    description VARCHAR(255)
);

CREATE TABLE template_replies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_id INT NOT NULL,
    keyword VARCHAR(100) NOT NULL,
    reply_text TEXT NOT NULL,
    FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE CASCADE
);

INSERT INTO templates (name, description) VALUES
('Mkahawa', 'Bot ya msingi kwa migahawa — menu, bei, order'),
('Saluni', 'Bot ya booking kwa saluni na spa'),
('Duka', 'Bot ya order na bei kwa maduka');

INSERT INTO template_replies (template_id, keyword, reply_text) VALUES
(1, 'habari', 'Karibu! Andika MENU kuona chakula chetu.'),
(1, 'menu', '1. Wali kuku\n2. Chips mayai\n3. Ndizi nyama\nAndika jina la chakula unachotaka.'),
(1, 'bei', 'Tafadhali tuambie chakula gani ili tukupe bei sahihi.'),
(2, 'habari', 'Karibu! Andika BOOK kuweka nafasi yako.'),
(2, 'book', 'Tafadhali tuambie siku na saa unayopenda kuja.');

-- Malipo ya wateja kwa Admin Mkuu
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    merchant_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(30), -- M-Pesa, Tigo Pesa, Bank...
    period_start DATE,
    period_end DATE,
    paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (merchant_id) REFERENCES merchants(id) ON DELETE CASCADE
);

-- Rate limiting kwa webhook (kuzuia matumizi mabaya / spam)
CREATE TABLE request_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(50) NOT NULL, -- namba ya mteja aliyetuma ujumbe
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_identifier_time (identifier, created_at)
);

-- Saa za kufanya kazi za bot + mipangilio ya ziada ya merchant
ALTER TABLE merchants
    ADD COLUMN working_hours_start TIME DEFAULT NULL,
    ADD COLUMN working_hours_end TIME DEFAULT NULL,
    ADD COLUMN outside_hours_message TEXT DEFAULT NULL;

-- HAKUNA admin wa default hapa kwa makusudi (si salama kuweka password
-- iliyowekwa mapema kwenye code inayoweza kuonekana na kila mtu).
-- Baada ya ku-import schema hii, fungua /public/install.php kwenye
-- browser yako MARA MOJA TU kuunda akaunti yako ya kwanza ya Admin.