-- Database Schema for Anjaneyulu.in CRM

-- Users Table
CREATE SEQUENCE IF NOT EXISTS user_unique_id_seq START WITH 100000;

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    unique_user_id BIGINT UNIQUE DEFAULT nextval('user_unique_id_seq'),
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(10) UNIQUE NOT NULL,
    whatsapp_number VARCHAR(10),
    email VARCHAR(100),
    gender VARCHAR(10),
    age INTEGER,
    occupation VARCHAR(100),
    photo_url TEXT,
    pan_card_url TEXT,
    aadhaar_front_url TEXT,
    aadhaar_back_url TEXT,
    present_address JSONB, -- {houseno, street, area, city, state, pincode}
    permanent_address JSONB, -- {houseno, street, area, city, state, pincode}
    referred_by_name VARCHAR(100),
    referred_by_mobile VARCHAR(10),
    additional_details TEXT,
    role VARCHAR(20) DEFAULT 'customer', -- super_admin, admin, loan_manager, associate_manager, associate, customer
    status VARCHAR(20) DEFAULT 'active', -- active, suspended
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Team Hierarchy Table
CREATE TABLE IF NOT EXISTS team_hierarchy (
    id SERIAL PRIMARY KEY,
    manager_id INTEGER REFERENCES users(id),
    associate_id INTEGER REFERENCES users(id),
    role_at_assignment VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(manager_id, associate_id)
);

-- Pincodes Table
CREATE TABLE IF NOT EXISTS pincodes (
    pincode VARCHAR(6) PRIMARY KEY,
    state VARCHAR(100),
    city VARCHAR(100),
    areas JSONB -- Array of area names
);

-- Leads Table (Freelancer & Personal Loan)
CREATE TABLE IF NOT EXISTS leads (
    id SERIAL PRIMARY KEY,
    lead_type VARCHAR(50) NOT NULL, -- 'freelancer', 'personal_loan'
    user_id INTEGER REFERENCES users(id),
    full_name VARCHAR(100) NOT NULL,
    mobile VARCHAR(10) NOT NULL,
    email VARCHAR(100),
    service_interested VARCHAR(100),
    loan_amount DECIMAL(15, 2),
    net_salary DECIMAL(15, 2),
    company_name VARCHAR(100),
    employment_type VARCHAR(50),
    city VARCHAR(100),
    state VARCHAR(50),
    pincode VARCHAR(6),
    status VARCHAR(50) DEFAULT 'New',
    sub_stage VARCHAR(100),
    summary TEXT,
    metadata JSONB, -- For any extra fields
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Properties Table
CREATE TABLE IF NOT EXISTS properties (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    property_type VARCHAR(100),
    price DECIMAL(20, 2),
    location_pincode VARCHAR(6),
    location_state VARCHAR(50),
    location_city VARCHAR(100),
    location_area VARCHAR(100),
    listing_name VARCHAR(100),
    listing_mobile VARCHAR(10),
    listing_by VARCHAR(50), -- Owner, Agent
    status VARCHAR(20) DEFAULT 'pending', -- pending, approved, rejected, sold
    hierarchy VARCHAR(50), -- Normal, Gated, etc.
    approvals JSONB, -- Array of approvals
    amenities JSONB, -- Array of amenities
    features JSONB, -- Specific features (BHK, Facing, etc.)
    voice_note TEXT, -- URL or status
    media JSONB, -- Array of image/PDF URLs
    allow_contact BOOLEAN DEFAULT TRUE,
    created_by INTEGER REFERENCES users(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Audit Logs Table
CREATE TABLE IF NOT EXISTS audit_logs (
    id SERIAL PRIMARY KEY,
    action_type VARCHAR(100) NOT NULL,
    actor_id INTEGER REFERENCES users(id),
    details JSONB,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Products Table
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(15, 2) NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'physical', 'digital', 'service'
    image_url TEXT,
    stock_quantity INTEGER DEFAULT 0, -- Relevant for physical products
    is_active BOOLEAN DEFAULT TRUE,
    metadata JSONB, -- For digital download links or service details
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Orders Table
CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total_amount DECIMAL(15, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'Pending', -- Pending, Processing, Shipped, Delivered, Completed, Cancelled
    payment_status VARCHAR(50) DEFAULT 'Pending', -- Pending, Paid, Failed
    shipping_address TEXT,
    contact_mobile VARCHAR(15),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Order Items Table
CREATE TABLE IF NOT EXISTS order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    price_at_purchase DECIMAL(15, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Migration to ensure existing tables have unique_user_id column
CREATE SEQUENCE IF NOT EXISTS user_unique_id_seq START WITH 100000;
ALTER TABLE users ADD COLUMN IF NOT EXISTS unique_user_id BIGINT UNIQUE DEFAULT nextval('user_unique_id_seq');

