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

SET SQL_MODE = 'NO_AUTO_VALUE_ON_ZERO';
START TRANSACTION;

CREATE TABLE IF NOT EXISTS agentsmrd (
  zone_order int(11) 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,
  KEY idx_agent_name (agent_name),
  KEY idx_rsm_name (rsm_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'rsm') NOT NULL DEFAULT 'rsm',
    active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_agent_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    agent_name VARCHAR(150) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_user_agent (user_id, agent_name),
    KEY idx_assignment_agent (agent_name),
    CONSTRAINT fk_assignment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visit_reports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_uuid CHAR(36) NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    date_of_visit DATETIME NOT NULL,
    agent_name VARCHAR(150) NOT NULL,
    accompanied_by VARCHAR(150) DEFAULT NULL,
    visited_type ENUM('Review', 'Audit', 'Training') NOT NULL,
    started_at DATETIME DEFAULT NULL,
    start_lat DECIMAL(10,7) DEFAULT NULL,
    start_lng DECIMAL(10,7) DEFAULT NULL,
    ended_at DATETIME DEFAULT NULL,
    end_lat DECIMAL(10,7) DEFAULT NULL,
    end_lng DECIMAL(10,7) DEFAULT NULL,
    status ENUM('draft', 'completed') NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_report_uuid (report_uuid),
    KEY idx_report_agent (agent_name),
    KEY idx_report_date (date_of_visit),
    KEY idx_report_status (status),
    CONSTRAINT fk_report_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS visit_form_progress (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    form_no TINYINT UNSIGNED NOT NULL,
    is_saved TINYINT(1) NOT NULL DEFAULT 1,
    saved_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uniq_report_form (report_id, form_no),
    CONSTRAINT fk_progress_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_db_profile (
    report_id BIGINT UNSIGNED NOT NULL,
    owner_contact VARCHAR(150) DEFAULT NULL,
    coverage_area VARCHAR(255) DEFAULT NULL,
    route_strength VARCHAR(100) DEFAULT NULL,
    vehicle_availability VARCHAR(100) DEFAULT NULL,
    log_book_updated ENUM('Yes', 'No') DEFAULT NULL,
    territory_route_map ENUM('Yes', 'No') DEFAULT NULL,
    territory_route_map_file VARCHAR(255) DEFAULT NULL,
    route_plan ENUM('Yes', 'No') DEFAULT NULL,
    no_credit_bills INT NOT NULL DEFAULT 0,
    credit_bill_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    no_cheques INT NOT NULL DEFAULT 0,
    cheque_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    cash_bank_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    progress_sheet_updated ENUM('Yes', 'No') DEFAULT NULL,
    progress_sheet_comment TEXT DEFAULT NULL,
    sku_sales_update ENUM('Low', 'Moderate', 'High') DEFAULT NULL,
    sku_sales_comment TEXT DEFAULT NULL,
    store_length DECIMAL(10,2) DEFAULT NULL,
    store_width DECIMAL(10,2) DEFAULT NULL,
    store_condition TEXT DEFAULT NULL,
    market_return_condition TEXT DEFAULT NULL,
    store_table_count INT NOT NULL DEFAULT 0,
    store_chair_count INT NOT NULL DEFAULT 0,
    store_comments TEXT DEFAULT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (report_id),
    CONSTRAINT fk_profile_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_sales_snapshot (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    category_name ENUM('Biscuits', 'Gem', 'Cracker', 'Chocolate', 'Snacks', 'Wafer') NOT NULL,
    mtd_target DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    mtd_achievement DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    variance DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    status_remarks VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_sales_report (report_id),
    CONSTRAINT fk_sales_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_stock_status (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    product_name VARCHAR(150) NOT NULL,
    stock_level INT NOT NULL DEFAULT 0,
    system_stock INT NOT NULL DEFAULT 0,
    variance INT NOT NULL DEFAULT 0,
    variance_comment TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_stock_report (report_id),
    CONSTRAINT fk_stock_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_issues (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    issue_type ENUM('Sales', 'Stock', 'Distribution', 'Team Discipline') NOT NULL,
    description TEXT NOT NULL,
    severity ENUM('N/A', 'Low', 'Medium', 'High') NOT NULL DEFAULT 'N/A',
    PRIMARY KEY (id),
    KEY idx_issue_report (report_id),
    CONSTRAINT fk_issue_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_actions_db (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    action_name VARCHAR(150) NOT NULL,
    comments TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_action_db_report (report_id),
    CONSTRAINT fk_action_db_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_actions_staff (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    staff_role ENUM('ASM', 'ASE', 'CSR') NOT NULL,
    comments TEXT DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_action_staff_report (report_id),
    CONSTRAINT fk_action_staff_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_follow_ups (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    report_id BIGINT UNSIGNED NOT NULL,
    action_name VARCHAR(150) NOT NULL,
    responsible_person VARCHAR(150) DEFAULT NULL,
    deadline_period ENUM('1 month', '3 months', '6 months') NOT NULL,
    PRIMARY KEY (id),
    KEY idx_follow_report (report_id),
    CONSTRAINT fk_follow_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS report_general_remarks (
    report_id BIGINT UNSIGNED NOT NULL,
    remark TEXT DEFAULT NULL,
    prepared_by_signature VARCHAR(255) DEFAULT NULL,
    distributor_signature VARCHAR(255) DEFAULT NULL,
    employee_signature VARCHAR(255) DEFAULT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (report_id),
    CONSTRAINT fk_general_report FOREIGN KEY (report_id) REFERENCES visit_reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO users (full_name, username, password_hash, role, active)
VALUES
    ('National Admin', 'admin', '$2b$10$A3zH/mGHkedMhp5Cp7U/T.uTDLwNqUUuJKpkVCw0aOr0roxbK5ElC', 'admin', 1),
    ('Regional Sales Manager', 'rsm1', '$2b$10$BrDg.5MuIRTmmuEPLkr0XeMLJrTUXm7asilgh3kDqketXALyQ0ODK', 'rsm', 1)
ON DUPLICATE KEY UPDATE full_name = VALUES(full_name);

COMMIT;

-- After importing your real agentsmrd data, assign DB names to the RSM user if needed.
-- Example:
-- INSERT INTO user_agent_assignments (user_id, agent_name)
-- SELECT 2, agent_name FROM agentsmrd WHERE rsm_name = 'Regional Sales Manager';
