-- ============================================================
-- DATABASE TAPAK (MySQL / MariaDB)
-- Sumber: Konsep Database TAPAK (MySQL), 13 tabel.
-- Target: MySQL 5.7+ (kolom JSON memerlukan MySQL 5.7+).
--
-- Catatan desain:
-- 1. Nama database, charset, dan collation ditetapkan oleh cli/setup-db.php
--    (utf8mb4 / utf8mb4_unicode_ci), bukan oleh berkas ini.
-- 2. Setiap tabel menggunakan InnoDB.
-- 3. Tidak ada tabel atau kolom di luar konsep.
-- 4. activity_logs sengaja tidak memiliki foreign key.
-- 5. Aturan bisnis yang tidak portabel sebagai CHECK divalidasi oleh backend.
-- 6. Berkas ini aman dijalankan berulang (CREATE TABLE IF NOT EXISTS).
-- ============================================================

-- 1. roles
CREATE TABLE IF NOT EXISTS roles (
    role_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_code VARCHAR(30) NOT NULL,
    role_name VARCHAR(100) NOT NULL,
    description VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id),
    UNIQUE KEY uq_roles_role_code (role_code)
) ENGINE=InnoDB;

-- 2. users
CREATE TABLE IF NOT EXISTS users (
    user_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    role_id BIGINT UNSIGNED NOT NULL,
    username VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email),
    CONSTRAINT fk_users_role
        FOREIGN KEY (role_id) REFERENCES roles (role_id)
) ENGINE=InnoDB;

-- 3. user_sessions
CREATE TABLE IF NOT EXISTS user_sessions (
    session_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    issued_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    revoked_at DATETIME NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (session_id),
    UNIQUE KEY uq_user_sessions_token_hash (token_hash),
    CONSTRAINT fk_user_sessions_user
        FOREIGN KEY (user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 4. teams
CREATE TABLE IF NOT EXISTS teams (
    team_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    team_name VARCHAR(100) NOT NULL,
    team_leader_user_id BIGINT UNSIGNED NOT NULL,
    manager_user_id BIGINT UNSIGNED NOT NULL,
    territory_name VARCHAR(150) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (team_id),
    UNIQUE KEY uq_teams_team_leader_user_id (team_leader_user_id),
    CONSTRAINT fk_teams_team_leader_user
        FOREIGN KEY (team_leader_user_id) REFERENCES users (user_id),
    CONSTRAINT fk_teams_manager_user
        FOREIGN KEY (manager_user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 5. team_members (user_id = PK sekaligus FK: satu staf, satu penempatan tim)
CREATE TABLE IF NOT EXISTS team_members (
    user_id BIGINT UNSIGNED NOT NULL,
    team_id BIGINT UNSIGNED NOT NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id),
    CONSTRAINT fk_team_members_user
        FOREIGN KEY (user_id) REFERENCES users (user_id),
    CONSTRAINT fk_team_members_team
        FOREIGN KEY (team_id) REFERENCES teams (team_id)
) ENGINE=InnoDB;

-- 6. customers
CREATE TABLE IF NOT EXISTS customers (
    customer_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    phone_normalized VARCHAR(15) NOT NULL,
    address TEXT NOT NULL,
    customer_status VARCHAR(20) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    notes TEXT NULL,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (customer_id),
    UNIQUE KEY uq_customers_phone_normalized (phone_normalized),
    CONSTRAINT fk_customers_created_by_user
        FOREIGN KEY (created_by_user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 7. visits
CREATE TABLE IF NOT EXISTS visits (
    visit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    visited_at DATETIME NOT NULL,
    result_code VARCHAR(50) NOT NULL,
    notes TEXT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    location_status VARCHAR(20) NOT NULL,
    location_captured_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (visit_id),
    CONSTRAINT fk_visits_user
        FOREIGN KEY (user_id) REFERENCES users (user_id),
    CONSTRAINT fk_visits_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
) ENGINE=InnoDB;

-- 8. photos (tepat satu dari visit_id atau customer_id harus terisi; divalidasi di backend)
CREATE TABLE IF NOT EXISTS photos (
    photo_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    visit_id BIGINT UNSIGNED NULL,
    customer_id BIGINT UNSIGNED NULL,
    uploaded_by_user_id BIGINT UNSIGNED NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    storage_path VARCHAR(500) NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_size_bytes BIGINT UNSIGNED NULL,
    upload_status VARCHAR(20) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    captured_at DATETIME NULL,
    uploaded_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (photo_id),
    CONSTRAINT fk_photos_visit
        FOREIGN KEY (visit_id) REFERENCES visits (visit_id),
    CONSTRAINT fk_photos_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT fk_photos_uploaded_by_user
        FOREIGN KEY (uploaded_by_user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 9. products
CREATE TABLE IF NOT EXISTS products (
    product_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    product_code VARCHAR(50) NULL,
    product_name VARCHAR(150) NOT NULL,
    unit VARCHAR(30) NOT NULL,
    current_price DECIMAL(15,2) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id),
    UNIQUE KEY uq_products_product_code (product_code)
) ENGINE=InnoDB;

-- 10. sales_transactions
CREATE TABLE IF NOT EXISTS sales_transactions (
    transaction_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_code VARCHAR(50) NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    seller_user_id BIGINT UNSIGNED NOT NULL,
    transaction_datetime DATETIME NOT NULL,
    total_amount DECIMAL(15,2) NOT NULL,
    status VARCHAR(30) NOT NULL,
    notes TEXT NULL,
    submitted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (transaction_id),
    UNIQUE KEY uq_sales_transactions_transaction_code (transaction_code),
    CONSTRAINT fk_sales_transactions_customer
        FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
    CONSTRAINT fk_sales_transactions_seller_user
        FOREIGN KEY (seller_user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 11. sales_transaction_items (unit_price = harga historis, bukan diambil ulang dari products)
CREATE TABLE IF NOT EXISTS sales_transaction_items (
    item_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_id BIGINT UNSIGNED NOT NULL,
    product_id BIGINT UNSIGNED NOT NULL,
    quantity DECIMAL(12,3) NOT NULL,
    unit_price DECIMAL(15,2) NOT NULL,
    subtotal DECIMAL(15,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (item_id),
    CONSTRAINT fk_sales_transaction_items_transaction
        FOREIGN KEY (transaction_id) REFERENCES sales_transactions (transaction_id),
    CONSTRAINT fk_sales_transaction_items_product
        FOREIGN KEY (product_id) REFERENCES products (product_id)
) ENGINE=InnoDB;

-- 12. transaction_reviews (riwayat seluruh keputusan pemeriksa)
CREATE TABLE IF NOT EXISTS transaction_reviews (
    review_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    transaction_id BIGINT UNSIGNED NOT NULL,
    reviewer_user_id BIGINT UNSIGNED NOT NULL,
    decision VARCHAR(20) NOT NULL,
    reason TEXT NULL,
    decided_at DATETIME NOT NULL,
    PRIMARY KEY (review_id),
    CONSTRAINT fk_transaction_reviews_transaction
        FOREIGN KEY (transaction_id) REFERENCES sales_transactions (transaction_id),
    CONSTRAINT fk_transaction_reviews_reviewer_user
        FOREIGN KEY (reviewer_user_id) REFERENCES users (user_id)
) ENGINE=InnoDB;

-- 13. activity_logs (sengaja TANPA foreign key; user_id bernilai biasa dan boleh NULL)
CREATE TABLE IF NOT EXISTS activity_logs (
    log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    username VARCHAR(100) NOT NULL,
    action_category VARCHAR(30) NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    entity_type VARCHAR(50) NULL,
    entity_id VARCHAR(64) NULL,
    description TEXT NULL,
    result_status VARCHAR(20) NOT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    request_method VARCHAR(10) NULL,
    endpoint VARCHAR(255) NULL,
    old_values JSON NULL,
    new_values JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (log_id)
) ENGINE=InnoDB;