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;

VALUES ('Beat Day View', 'View all beats running for a user on a date', 175, 6, 'beat-plan-dayview');