-- ============================================
-- PERSONAL AI COMPANION - DATABASE BLUEPRINT
-- Engine: MySQL 5.7+ (shared hosting compatible)
-- ============================================

CREATE DATABASE IF NOT EXISTS personal_ai CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE personal_ai;

-- ============================================
-- 1. USER (hanya 1 user = kamu sendiri)
-- ============================================
CREATE TABLE user (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL DEFAULT 'User',
    wa_number   VARCHAR(20)   NOT NULL,           -- format: 6281234567890
    timezone    VARCHAR(50)   DEFAULT 'Asia/Jakarta',
    created_at  DATETIME      DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 2. MEMORIES - semua yang AI "ingat"
-- ============================================
CREATE TABLE memories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    category    ENUM('secret','note','person','preference','fact','goal') NOT NULL,
    key_name    VARCHAR(255)  NOT NULL,            -- "pw gmail", "nama pacar", "warna favorit"
    value       TEXT          NOT NULL,            -- isi (secret: terenkripsi AES-256)
    is_encrypted TINYINT(1)   DEFAULT 0,
    tags        VARCHAR(255)  NULL,                -- "kerja,penting" untuk filter
    created_at  DATETIME      DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_keyname  (key_name)
);

-- ============================================
-- 3. EVENTS - jadwal & reminder
-- ============================================
CREATE TABLE events (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT         NULL,
    event_datetime  DATETIME     NOT NULL,
    remind_h1       TINYINT(1)   DEFAULT 1,   -- ingatkan H-1 jam 08.00
    remind_h0       TINYINT(1)   DEFAULT 1,   -- ingatkan hari H jam 07.00
    remind_1h       TINYINT(1)   DEFAULT 1,   -- ingatkan 1 jam sebelum
    remind_30m      TINYINT(1)   DEFAULT 0,   -- ingatkan 30 menit sebelum
    notif_sent      JSON         NULL,         -- {"h1":true,"h0":true,"1h":false}
    is_done         TINYINT(1)   DEFAULT 0,
    created_at      DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_datetime (event_datetime),
    INDEX idx_done     (is_done)
);

-- ============================================
-- 4. CONVERSATIONS - riwayat chat
-- ============================================
CREATE TABLE conversations (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    role        ENUM('user','assistant') NOT NULL,
    content     TEXT         NOT NULL,
    is_summarized TINYINT(1) DEFAULT 0,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_summarized (is_summarized),
    INDEX idx_created    (created_at)
);

-- ============================================
-- 5. SUMMARIES - ringkasan konteks per periode
-- ============================================
CREATE TABLE summaries (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    content     TEXT         NOT NULL,           -- ringkasan dari AI
    covers_from DATETIME     NOT NULL,
    covers_to   DATETIME     NOT NULL,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- 6. LOGS - debug & audit
-- ============================================
CREATE TABLE logs (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    type        ENUM('incoming','outgoing','reminder','error','system') NOT NULL,
    message     TEXT         NOT NULL,
    meta        JSON         NULL,
    created_at  DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_type    (type),
    INDEX idx_created (created_at)
);

-- ============================================
-- INSERT: data awal user
-- ============================================
INSERT INTO user (name, wa_number, timezone)
VALUES ('Nama Kamu', '6281234567890', 'Asia/Jakarta');