-- ============================================
-- SAVYASACHI CRM PRO
-- Complete Database Structure
-- MySQL 8.0+
-- ============================================

CREATE DATABASE IF NOT EXISTS juiaqyya_savyasachi_crm;
USE juiaqyya_savyasachi_crm;

-- ============================================
-- USERS
-- ============================================

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('Admin','Manager','Unit Incharge') DEFAULT 'Unit Incharge',
    mobile VARCHAR(20),
    email VARCHAR(100),
    status ENUM('Active','Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Default Admin
INSERT INTO users
(full_name, username, password, role)
VALUES
('Administrator','admin',
'$2y$10$abcdefghijklmnopqrstuv',
'Admin');

-- ============================================
-- UNITS
-- ============================================

CREATE TABLE units (
    id INT PRIMARY KEY,
    unit_name VARCHAR(100) NOT NULL,
    incharge_name VARCHAR(100),
    incharge_mobile VARCHAR(20),
    cug_mobile VARCHAR(20),
    address TEXT,
    status ENUM('Active','Inactive') DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- DAILY FOLLOWUPS
-- ============================================

CREATE TABLE daily_followups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    followup_date DATE NOT NULL,
    unit_id INT,
    total_followups INT DEFAULT 0,
    walkins INT DEFAULT 0,
    commitments INT DEFAULT 0,
    conversions INT DEFAULT 0,
    remarks TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (unit_id) REFERENCES units(id)
);

-- ============================================
-- LEADS MANAGEMENT
-- ============================================

CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_name VARCHAR(100),
    mobile VARCHAR(20),
    alternate_mobile VARCHAR(20),
    email VARCHAR(100),
    gender VARCHAR(20),
    age INT,
    unit_id INT,
    lead_source VARCHAR(100),

    status ENUM(
        'New',
        'Interested',
        'Followup Pending',
        'Converted',
        'Lost'
    ) DEFAULT 'New',

    next_followup_date DATE,
    remarks TEXT,
    assigned_to INT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (unit_id) REFERENCES units(id),
    FOREIGN KEY (assigned_to) REFERENCES users(id)
);

-- ============================================
-- LEAD FOLLOWUP HISTORY
-- ============================================

CREATE TABLE lead_followups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_id INT,
    followup_date DATE,
    followup_type VARCHAR(50),
    remarks TEXT,
    next_followup_date DATE,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (lead_id) REFERENCES leads(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- ============================================
-- CUSTOMERS
-- ============================================

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),
    mobile VARCHAR(20),
    email VARCHAR(100),
    gender VARCHAR(20),
    age INT,
    address TEXT,
    unit_id INT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (unit_id) REFERENCES units(id)
);

-- ============================================
-- CUSTOMER PURCHASES
-- ============================================

CREATE TABLE customer_purchases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    bill_no VARCHAR(50),
    purchase_date DATE,
    amount DECIMAL(12,2),
    remarks TEXT,

    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
);

-- ============================================
-- ATTENDANCE
-- ============================================

CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    attendance_date DATE,
    check_in TIME,
    check_out TIME,
    status ENUM(
        'Present',
        'Absent',
        'Leave',
        'Half Day'
    ) DEFAULT 'Present',

    remarks TEXT,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

-- ============================================
-- REVENUE
-- ============================================

CREATE TABLE revenue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    revenue_date DATE,
    unit_id INT,
    sales_amount DECIMAL(12,2) DEFAULT 0,
    prescription_sales DECIMAL(12,2) DEFAULT 0,
    otc_sales DECIMAL(12,2) DEFAULT 0,
    expenses DECIMAL(12,2) DEFAULT 0,
    net_profit DECIMAL(12,2) DEFAULT 0,

    FOREIGN KEY (unit_id)
    REFERENCES units(id)
);

-- ============================================
-- MARKETING TRACKER
-- ============================================

CREATE TABLE marketing_tracker (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_name VARCHAR(200),
    campaign_date DATE,

    platform ENUM(
        'WhatsApp',
        'Facebook',
        'Instagram',
        'Google',
        'SMS',
        'Offline'
    ),

    reach_count INT DEFAULT 0,
    leads_generated INT DEFAULT 0,
    conversions INT DEFAULT 0,
    cost DECIMAL(12,2) DEFAULT 0,
    remarks TEXT
);

-- ============================================
-- GOOGLE BUSINESS LEADS
-- ============================================

CREATE TABLE google_business_leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lead_date DATE,
    unit_id INT,

    call_leads INT DEFAULT 0,
    direction_requests INT DEFAULT 0,
    website_clicks INT DEFAULT 0,
    whatsapp_clicks INT DEFAULT 0,

    remarks TEXT,

    FOREIGN KEY (unit_id)
    REFERENCES units(id)
);

-- ============================================
-- TARGETS
-- ============================================

CREATE TABLE targets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_month VARCHAR(20),
    target_year INT,

    unit_id INT,

    followup_target INT DEFAULT 0,
    walkin_target INT DEFAULT 0,
    commitment_target INT DEFAULT 0,
    revenue_target DECIMAL(12,2) DEFAULT 0,

    FOREIGN KEY (unit_id)
    REFERENCES units(id)
);

-- ============================================
-- NOTIFICATIONS
-- ============================================

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    message TEXT,

    user_id INT,

    status ENUM('Unread','Read')
    DEFAULT 'Unread',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
    REFERENCES users(id)
);

-- ============================================
-- HEALTH CAMPS
-- ============================================

CREATE TABLE health_camps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    camp_name VARCHAR(255),
    camp_date DATE,
    location VARCHAR(255),

    visitors INT DEFAULT 0,
    leads_generated INT DEFAULT 0,
    conversions INT DEFAULT 0,

    remarks TEXT
);

-- ============================================
-- SETTINGS
-- ============================================

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(255),
    company_mobile VARCHAR(20),
    company_email VARCHAR(100),
    company_address TEXT,
    logo VARCHAR(255)
);

-- ============================================
-- BACKUP LOGS
-- ============================================

CREATE TABLE backup_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    backup_file VARCHAR(255),
    backup_date DATETIME,
    backup_by INT,

    FOREIGN KEY (backup_by)
    REFERENCES users(id)
);

-- ============================================
-- INDEXES
-- ============================================

CREATE INDEX idx_followup_date
ON daily_followups(followup_date);

CREATE INDEX idx_lead_mobile
ON leads(mobile);

CREATE INDEX idx_customer_mobile
ON customers(mobile);

CREATE INDEX idx_revenue_date
ON revenue(revenue_date);

CREATE INDEX idx_attendance_date
ON attendance(attendance_date);

-- ============================================
-- END OF DATABASE
-- ============================================