CREATE DATABASE IF NOT EXISTS rsm_db;
USE rsm_db;

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'rsm') NOT NULL,
    full_name VARCHAR(100),
    rsm_id VARCHAR(50) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Agents table (as provided)
CREATE TABLE IF NOT EXISTS agentsmrd (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zone_order INT NOT NULL,
    sap_code VARCHAR(15) NOT NULL,
    chanelname VARCHAR(15) NOT NULL,
    sm_id VARCHAR(10) NOT NULL,
    rm_id VARCHAR(50) NOT NULL,
    zone VARCHAR(30) DEFAULT NULL,
    region VARCHAR(40) DEFAULT NULL,
    sub_region VARCHAR(100) NOT NULL,
    arm_name VARCHAR(100) NOT NULL,
    asm_name VARCHAR(100) NOT NULL,
    rsm_name VARCHAR(50) DEFAULT NULL,
    executive_name VARCHAR(50) DEFAULT NULL,
    agent_name VARCHAR(150) DEFAULT NULL,
    agent_replc VARCHAR(150) NOT NULL,
    agent_replcnames VARCHAR(150) NOT NULL,
    agent_type VARCHAR(30) DEFAULT NULL,
    range_type VARCHAR(50) NOT NULL,
    rep_name VARCHAR(10) DEFAULT NULL,
    territory_name VARCHAR(50) DEFAULT NULL,
    stores_name VARCHAR(100) DEFAULT NULL,
    stores_type VARCHAR(30) DEFAULT NULL,
    user_names VARCHAR(50) DEFAULT NULL,
    user_dates DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    dbaddres VARCHAR(100) NOT NULL,
    dbcontact VARCHAR(100) NOT NULL,
    dis_rates DOUBLE NOT NULL,
    INDEX (agent_name),
    INDEX (rsm_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Main Visits table
CREATE TABLE IF NOT EXISTS visits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    agent_name VARCHAR(150) NOT NULL,
    visit_date DATE NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NULL,
    start_lat DECIMAL(10, 8),
    start_lng DECIMAL(11, 8),
    end_lat DECIMAL(10, 8),
    end_lng DECIMAL(11, 8),
    accompanied_by VARCHAR(255),
    visited_type ENUM('Review', 'Audit', 'Training'),
    status ENUM('draft', 'submitted') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_visit (user_id, agent_name, visit_date),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 02: DB Profile
CREATE TABLE IF NOT EXISTS form_db_profile (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT UNIQUE NOT NULL,
    db_owner_contact VARCHAR(255),
    coverage_area VARCHAR(255),
    route_strength VARCHAR(255),
    vehicle_availability VARCHAR(255),
    log_book_update TINYINT(1),
    log_book_img VARCHAR(255),
    territory_map TINYINT(1),
    territory_map_img VARCHAR(255),
    route_plan TINYINT(1),
    route_plan_img VARCHAR(255),
    credit_bills_count INT,
    credit_bills_amount DECIMAL(15, 2),
    cheques_count INT,
    cheques_amount DECIMAL(15, 2),
    cash_in_hand DECIMAL(15, 2),
    progress_sheet_update TINYINT(1),
    progress_sheet_comment TEXT,
    progress_sheet_img VARCHAR(255),
    sku_sales_update ENUM('low', 'moderate', 'high'),
    sku_sales_comment TEXT,
    store_length DECIMAL(10, 2),
    store_width DECIMAL(10, 2),
    store_condition VARCHAR(255),
    store_table_count INT,
    store_chair_count INT,
    store_comments TEXT,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 03: Sales Snapshot
CREATE TABLE IF NOT EXISTS form_sales_snapshot (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    category VARCHAR(50),
    mtd_target DECIMAL(15, 2),
    mtd_achievement DECIMAL(15, 2),
    variance DECIMAL(15, 2),
    remarks TEXT,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 04: Stock Status
CREATE TABLE IF NOT EXISTS form_stock_status (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    category VARCHAR(50),
    product_name VARCHAR(255),
    stock_level INT,
    system_stock INT,
    variance INT,
    comment TEXT,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 05: Issue Identify
CREATE TABLE IF NOT EXISTS form_issue_identify (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT UNIQUE NOT NULL,
    issue_type ENUM('Sales', 'Stock', 'Distribution', 'Team Discipline'),
    description TEXT,
    severity ENUM('N/A', 'low', 'Medium', 'High'),
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 06: Action Agreed
CREATE TABLE IF NOT EXISTS form_action_agreed (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    target_type ENUM('DB', 'Staff'),
    action_item VARCHAR(255),
    comment TEXT,
    staff_role ENUM('ASM', 'ASE', 'CSR') NULL,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 07: Follow Up
CREATE TABLE IF NOT EXISTS form_follow_up (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT NOT NULL,
    action VARCHAR(255),
    responsible VARCHAR(255),
    deadline ENUM('1 month', '3 months', '6 months'),
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Form 08: General Remark & Signatures
CREATE TABLE IF NOT EXISTS form_general_remark (
    id INT AUTO_INCREMENT PRIMARY KEY,
    visit_id INT UNIQUE NOT NULL,
    remark TEXT,
    prepared_by_sig TEXT, -- Base64 or path
    distributor_sig TEXT,
    employee_sig TEXT,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed initial admin user (password: admin123)
INSERT INTO users (username, password, role, full_name) VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'System Administrator');