Subversion Repositories SmartDukaan

CREATE TABLE user.lead_live_location
(
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    lead_id            INT         NOT NULL,
    latitude           DOUBLE      NOT NULL,
    longitude          DOUBLE      NOT NULL,
    image_document_id  INT,
    mobile_number      VARCHAR(15) NOT NULL,
    status             VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    reviewed_by        INT                  DEFAULT 0,
    reviewed_timestamp DATETIME,
    submission_count   INT         NOT NULL DEFAULT 1,
    remark             VARCHAR(500),
    created_timestamp  DATETIME    NOT NULL,
    updated_timestamp  DATETIME    NOT NULL,
    INDEX idx_ll_lead_id (lead_id),
    INDEX idx_ll_lead_status (lead_id, status)
);


-- Table 1: beat (master)
CREATE TABLE user.beat
(
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(200),
    auth_user_id        INT      NOT NULL,
    beat_color          VARCHAR(20),
    start_location_name VARCHAR(200),
    start_latitude      VARCHAR(20),
    start_longitude     VARCHAR(20),
    total_days          INT     DEFAULT 1,
    active              BOOLEAN DEFAULT TRUE,
    created_by          INT,
    created_timestamp   DATETIME NOT NULL,
    INDEX idx_beat_auth (auth_user_id)
);

-- Table 2: beat_route (partner stops)
CREATE TABLE user.beat_route
(
    id             INT AUTO_INCREMENT PRIMARY KEY,
    beat_id        INT NOT NULL,
    fofo_id        INT NOT NULL,
    sequence_order INT NOT NULL,
    day_number     INT     DEFAULT 1,
    active         BOOLEAN DEFAULT TRUE,
    INDEX idx_br_beat (beat_id)
);

-- Table 3: beat_schedule (when beats run)
CREATE TABLE user.beat_schedule
(
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    beat_id            INT      NOT NULL,
    start_date         DATE     NOT NULL,
    end_date           DATE,
    day_number         INT         DEFAULT 1,
    total_distance_km  DOUBLE,
    total_time_mins    INT,
    end_action         VARCHAR(20) DEFAULT 'HOME',
    stay_location_name VARCHAR(200),
    stay_latitude      VARCHAR(20),
    stay_longitude     VARCHAR(20),
    created_timestamp  DATETIME NOT NULL,
    INDEX idx_bs_beat (beat_id),
    INDEX idx_bs_date (start_date)
);

-- Table 4: lead_route (leads in beats)
CREATE TABLE user.lead_route
(
    id                 INT AUTO_INCREMENT PRIMARY KEY,
    beat_id            INT      NOT NULL,
    lead_id            INT      NOT NULL,
    nearest_store_id   INT,
    schedule_date      DATE     NOT NULL,
    sequence_order     INT,
    status             VARCHAR(20) DEFAULT 'PENDING',
    approved_by        INT         DEFAULT 0,
    requested_by       INT         DEFAULT 0,
    approved_timestamp DATETIME,
    remark             VARCHAR(500),
    created_timestamp  DATETIME NOT NULL,
    updated_timestamp  DATETIME NOT NULL,
    INDEX idx_lr_beat (beat_id),
    INDEX idx_lr_lead (lead_id),
    INDEX idx_lr_status (status)
);


-- Drop old tables (only after verifying new tables work)
DROP TABLE IF EXISTS user.beat_plan;
DROP TABLE IF EXISTS user.beat_plan_day;
DROP TABLE IF EXISTS user.lead_visit_approval;

UPDATE auth.menu
SET display_text = 'Lead Visits'
WHERE action_class = 'visit-approvals';

ALTER TABLE user.beat
    ADD UNIQUE KEY uk_beat_user_name (auth_user_id, name);