-- =====================================================
-- JR SOLUTIONS PROPERTY VALUATION PORTAL
-- PostgreSQL 18+ Database Schema
-- Version: 1.0
-- Date: 2025-12-21
-- =====================================================

-- =====================================================
-- SECTION 1: EXTENSIONS
-- =====================================================

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Enable PostGIS for spatial data
CREATE EXTENSION IF NOT EXISTS "postgis";

-- =====================================================
-- SECTION 2: HELPER FUNCTIONS & TRIGGERS
-- =====================================================

-- Function to automatically update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to log data changes (Audit Trail)
CREATE OR REPLACE FUNCTION log_data_changes()
RETURNS TRIGGER AS $$
DECLARE
    current_user_id UUID;
BEGIN
    -- Get current user from session variable (set by application)
    BEGIN
        current_user_id := current_setting('app.current_user_id')::UUID;
    EXCEPTION WHEN OTHERS THEN
        current_user_id := NULL;
    END;
    
    IF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_logs (
            user_id,
            valuation_id,
            table_name,
            record_id,
            operation,
            old_value,
            new_value,
            changed_fields
        )
        VALUES (
            current_user_id,
            COALESCE(NEW.valuation_id, OLD.valuation_id),
            TG_TABLE_NAME,
            OLD.id,
            'UPDATE',
            to_jsonb(OLD),
            to_jsonb(NEW),
            (SELECT array_agg(key) FROM jsonb_each(to_jsonb(NEW)) WHERE to_jsonb(OLD) ->> key IS DISTINCT FROM to_jsonb(NEW) ->> key)
        );
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_logs (
            user_id,
            valuation_id,
            table_name,
            record_id,
            operation,
            new_value
        )
        VALUES (
            current_user_id,
            NEW.valuation_id,
            TG_TABLE_NAME,
            NEW.id,
            'INSERT',
            to_jsonb(NEW)
        );
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_logs (
            user_id,
            valuation_id,
            table_name,
            record_id,
            operation,
            old_value
        )
        VALUES (
            current_user_id,
            OLD.valuation_id,
            TG_TABLE_NAME,
            OLD.id,
            'DELETE',
            to_jsonb(OLD)
        );
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- SECTION 3: USER & SECURITY TABLES
-- =====================================================

-- Teams table (must come before users due to FK)
CREATE TABLE teams (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    team_name VARCHAR(200) UNIQUE NOT NULL,
    team_code VARCHAR(50) UNIQUE NOT NULL,
    team_description TEXT,
    team_lead_id UUID, -- FK added later
    coverage_states JSONB,
    coverage_districts JSONB,
    auto_assign_by_location BOOLEAN DEFAULT false,
    auto_assign_by_workload BOOLEAN DEFAULT false,
    max_active_assignments_per_member INTEGER DEFAULT 10,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_teams_code ON teams(team_code);
CREATE INDEX idx_teams_status ON teams(status) WHERE status = 'active';

-- Users table
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    employee_id VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    mobile VARCHAR(20) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    password_salt TEXT NOT NULL,
    password_changed_at TIMESTAMP WITH TIME ZONE,
    password_expires_at TIMESTAMP WITH TIME ZONE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    photo_url TEXT,
    team_id UUID REFERENCES teams(id),
    reports_to UUID REFERENCES users(id),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'locked')),
    account_expires_at DATE,
    two_factor_enabled BOOLEAN DEFAULT false,
    two_factor_secret TEXT,
    failed_login_attempts INTEGER DEFAULT 0,
    locked_until TIMESTAMP WITH TIME ZONE,
    preferences JSONB,
    allowed_banks JSONB,
    allowed_locations JSONB,
    ip_whitelist TEXT[],
    working_hours_from TIME,
    working_hours_to TIME,
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_login_ip VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_users_employee_id ON users(employee_id);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_mobile ON users(mobile);
CREATE INDEX idx_users_team ON users(team_id);
CREATE INDEX idx_users_status ON users(status) WHERE status = 'active';
CREATE INDEX idx_users_deleted ON users(deleted_at) WHERE deleted_at IS NULL;

CREATE TRIGGER set_updated_at_users
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Add team_lead_id FK constraint (after users table exists)
ALTER TABLE teams ADD CONSTRAINT fk_teams_lead FOREIGN KEY (team_lead_id) REFERENCES users(id);
CREATE INDEX idx_teams_lead ON teams(team_lead_id);

-- Roles table
CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    role_name VARCHAR(100) UNIQUE NOT NULL,
    role_code VARCHAR(50) UNIQUE NOT NULL,
    role_description TEXT,
    permissions JSONB NOT NULL,
    role_level INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_roles_code ON roles(role_code);
CREATE INDEX idx_roles_status ON roles(status) WHERE status = 'active';

-- User-Roles junction table
CREATE TABLE user_roles (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    is_primary BOOLEAN DEFAULT false,
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    assigned_by UUID REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)
);

CREATE INDEX idx_user_roles_user ON user_roles(user_id);
CREATE INDEX idx_user_roles_role ON user_roles(role_id);

-- User sessions table
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    refresh_token TEXT UNIQUE NOT NULL,
    access_token_jti TEXT UNIQUE NOT NULL,
    device_type VARCHAR(50),
    device_name VARCHAR(200),
    browser VARCHAR(100),
    operating_system VARCHAR(100),
    ip_address VARCHAR(50),
    location VARCHAR(200),
    is_active BOOLEAN DEFAULT true,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    last_activity_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT valid_expiry CHECK (expires_at > created_at)
);

CREATE INDEX idx_user_sessions_user ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_refresh_token ON user_sessions(refresh_token);
CREATE INDEX idx_user_sessions_active ON user_sessions(is_active) WHERE is_active = true;
CREATE INDEX idx_user_sessions_expires ON user_sessions(expires_at);

-- =====================================================
-- SECTION 4: REFERENCE/LOOKUP TABLES
-- =====================================================

-- Locations table (Hierarchical: State > District > Mandal > Village)
CREATE TABLE locations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_type VARCHAR(20) NOT NULL CHECK (location_type IN ('state', 'district', 'mandal', 'village')),
    parent_id UUID REFERENCES locations(id),
    location_name VARCHAR(200) NOT NULL,
    location_code VARCHAR(50),
    pincodes TEXT[],
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_locations_type ON locations(location_type);
CREATE INDEX idx_locations_parent ON locations(parent_id);
CREATE INDEX idx_locations_name ON locations(location_name);
CREATE INDEX idx_locations_code ON locations(location_code);

-- Banks table
CREATE TABLE banks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bank_name VARCHAR(200) UNIQUE NOT NULL,
    bank_code VARCHAR(50) UNIQUE NOT NULL,
    logo_url TEXT,
    sla_days INTEGER DEFAULT 5,
    multi_level_approval BOOLEAN DEFAULT false,
    number_of_approval_levels INTEGER DEFAULT 1 CHECK (number_of_approval_levels BETWEEN 1 AND 3),
    contact_person VARCHAR(200),
    contact_email VARCHAR(200),
    contact_mobile VARCHAR(20),
    address TEXT,
    report_template_url TEXT,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_banks_code ON banks(bank_code);
CREATE INDEX idx_banks_status ON banks(status) WHERE status = 'active';

-- Bank configurations table
CREATE TABLE bank_configs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    bank_id UUID NOT NULL REFERENCES banks(id) ON DELETE CASCADE,
    config_type VARCHAR(50) NOT NULL CHECK (config_type IN ('field_config', 'validation_rules', 'workflow_config', 'document_checklist', 'valuation_config')),
    config_data JSONB NOT NULL,
    version INTEGER DEFAULT 1,
    is_active BOOLEAN DEFAULT true,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_active_config UNIQUE (bank_id, config_type, is_active) WHERE is_active = true
);

CREATE INDEX idx_bank_configs_bank ON bank_configs(bank_id);
CREATE INDEX idx_bank_configs_type ON bank_configs(config_type);

-- Document types table
CREATE TABLE document_types (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    document_type_name VARCHAR(200) UNIQUE NOT NULL,
    document_category VARCHAR(50) NOT NULL CHECK (document_category IN ('legal', 'financial', 'technical', 'other')),
    is_mandatory BOOLEAN DEFAULT false,
    bank_specific JSONB,
    property_type_specific JSONB,
    allowed_file_formats TEXT[],
    max_file_size_mb INTEGER DEFAULT 10,
    display_order INTEGER DEFAULT 0,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_document_types_category ON document_types(document_category);
CREATE INDEX idx_document_types_status ON document_types(status) WHERE status = 'active';

-- Rates table (Guideline & Market rates)
CREATE TABLE rates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    location_id UUID NOT NULL REFERENCES locations(id),
    rate_type VARCHAR(50) NOT NULL CHECK (rate_type IN ('tgiic', 'apiic', 'agri_guideline', 'market')),
    rate_per_unit DECIMAL(15, 2) NOT NULL,
    unit VARCHAR(20) NOT NULL CHECK (unit IN ('sqft', 'sqyd', 'sqmtr', 'acre')),
    effective_from DATE NOT NULL,
    effective_to DATE,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    updated_by UUID NOT NULL REFERENCES users(id),
    remarks TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT valid_date_range CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

CREATE INDEX idx_rates_location ON rates(location_id);
CREATE INDEX idx_rates_type ON rates(rate_type);
CREATE INDEX idx_rates_effective ON rates(effective_from, effective_to);
CREATE INDEX idx_rates_status ON rates(status) WHERE status = 'active';

-- Lookup values table (Generic dropdowns)
CREATE TABLE lookup_values (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    category VARCHAR(100) NOT NULL,
    value VARCHAR(200) NOT NULL,
    label VARCHAR(200) NOT NULL,
    parent_id UUID REFERENCES lookup_values(id),
    display_order INTEGER DEFAULT 0,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive')),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_category_value UNIQUE (category, value)
);

CREATE INDEX idx_lookup_values_category ON lookup_values(category);
CREATE INDEX idx_lookup_values_parent ON lookup_values(parent_id);
CREATE INDEX idx_lookup_values_status ON lookup_values(status) WHERE status = 'active';

-- =====================================================
-- SECTION 5: CORE DOMAIN TABLES
-- =====================================================

-- Valuations table (Main entity)
CREATE TABLE valuations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_number VARCHAR(50) UNIQUE NOT NULL,
    bank_id UUID NOT NULL REFERENCES banks(id),
    property_type VARCHAR(50) NOT NULL CHECK (property_type IN ('plot', 'independent_house', 'apartment')),
    status VARCHAR(50) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'survey', 'submitted', 'review', 'approval', 'approved', 'rejected', 'on_hold')),
    priority VARCHAR(20) NOT NULL DEFAULT 'medium' CHECK (priority IN ('high', 'medium', 'low')),
    created_by UUID NOT NULL REFERENCES users(id),
    assigned_surveyor UUID REFERENCES users(id),
    assigned_keyin UUID REFERENCES users(id),
    assigned_reviewer UUID REFERENCES users(id),
    assigned_approver UUID REFERENCES users(id),
    valuation_purpose VARCHAR(50),
    valuation_type VARCHAR(50),
    target_completion_date DATE,
    sla_days INTEGER DEFAULT 5,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    submitted_at TIMESTAMP WITH TIME ZONE,
    review_started_at TIMESTAMP WITH TIME ZONE,
    review_completed_at TIMESTAMP WITH TIME ZONE,
    approval_started_at TIMESTAMP WITH TIME ZONE,
    approved_at TIMESTAMP WITH TIME ZONE,
    rejected_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB
);

CREATE INDEX idx_valuations_number ON valuations(valuation_number);
CREATE INDEX idx_valuations_bank ON valuations(bank_id);
CREATE INDEX idx_valuations_status ON valuations(status);
CREATE INDEX idx_valuations_created_by ON valuations(created_by);
CREATE INDEX idx_valuations_assigned_surveyor ON valuations(assigned_surveyor);
CREATE INDEX idx_valuations_created_at ON valuations(created_at DESC);
CREATE INDEX idx_valuations_deleted_at ON valuations(deleted_at) WHERE deleted_at IS NULL;

CREATE TRIGGER set_updated_at_valuations
    BEFORE UPDATE ON valuations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Properties table
CREATE TABLE properties (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    survey_number VARCHAR(100),
    plot_number VARCHAR(100),
    house_number VARCHAR(100),
    building_name VARCHAR(200),
    apartment_name VARCHAR(200),
    flat_number VARCHAR(50),
    floor INTEGER,
    floor_level_from_ground INTEGER,
    address TEXT NOT NULL,
    pincode VARCHAR(10) NOT NULL,
    state_id UUID NOT NULL REFERENCES locations(id),
    district_id UUID NOT NULL REFERENCES locations(id),
    mandal_id UUID NOT NULL REFERENCES locations(id),
    village_id UUID REFERENCES locations(id),
    colony_name VARCHAR(200),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    coordinate_accuracy INTEGER,
    coordinate_capture_method VARCHAR(50),
    nearby_landmark TEXT,
    railway_station_name VARCHAR(200),
    railway_station_distance DECIMAL(10, 2),
    railway_station_distance_unit VARCHAR(10) DEFAULT 'km',
    bus_stand_name VARCHAR(200),
    bus_stand_distance DECIMAL(10, 2),
    bus_stand_distance_unit VARCHAR(10) DEFAULT 'km',
    hospital_name VARCHAR(200),
    hospital_distance DECIMAL(10, 2),
    hospital_distance_unit VARCHAR(10) DEFAULT 'km',
    institution_name VARCHAR(200),
    institution_distance DECIMAL(10, 2),
    institution_distance_unit VARCHAR(10) DEFAULT 'km',
    branch_office_distance DECIMAL(10, 2),
    branch_office_distance_unit VARCHAR(10) DEFAULT 'km',
    city_center_distance DECIMAL(10, 2),
    city_center_distance_unit VARCHAR(10) DEFAULT 'km',
    nearest_major_road TEXT,
    office_location_distance DECIMAL(10, 2),
    office_location_distance_unit VARCHAR(10) DEFAULT 'km',
    locality_classification VARCHAR(50) CHECK (locality_classification IN ('posh', 'upper_middle_class', 'middle_class', 'lower_middle_class')),
    locality_type VARCHAR(50) CHECK (locality_type IN ('urban', 'rural', 'semi_urban')),
    vicinity_description TEXT,
    suburb_name VARCHAR(200),
    civic_amenities_range VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_properties_valuation ON properties(valuation_id);
CREATE INDEX idx_properties_state ON properties(state_id);
CREATE INDEX idx_properties_district ON properties(district_id);
CREATE INDEX idx_properties_mandal ON properties(mandal_id);
CREATE INDEX idx_properties_pincode ON properties(pincode);

CREATE TRIGGER set_updated_at_properties
    BEFORE UPDATE ON properties
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Applicants table
CREATE TABLE applicants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    applicant_name VARCHAR(200) NOT NULL,
    co_applicant_name VARCHAR(200),
    owner_name VARCHAR(200) NOT NULL,
    contact_mobile VARCHAR(20) NOT NULL,
    contact_email VARCHAR(200),
    representative_name VARCHAR(200),
    person_met_at_site VARCHAR(200),
    inspection_done_by UUID REFERENCES users(id),
    inspection_date DATE,
    inspection_time TIME,
    weather_conditions VARCHAR(100),
    bank_specific_data JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_applicants_valuation ON applicants(valuation_id);
CREATE INDEX idx_applicants_mobile ON applicants(contact_mobile);

CREATE TRIGGER set_updated_at_applicants
    BEFORE UPDATE ON applicants
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Property details table
CREATE TABLE property_details (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID UNIQUE NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    shape_of_site VARCHAR(50),
    level VARCHAR(50),
    development_status VARCHAR(50),
    infrastructure_quality VARCHAR(50),
    construction_activity VARCHAR(50),
    nature_of_site VARCHAR(50),
    site_limits VARCHAR(50),
    negatives_to_locality TEXT,
    positives_to_locality TEXT,
    possibility_of_flooding BOOLEAN,
    town_planning_approved BOOLEAN,
    corner_or_intermittent VARCHAR(20),
    water_potentiality BOOLEAN,
    underground_sewerage BOOLEAN,
    power_supply_available BOOLEAN,
    has_road_access BOOLEAN NOT NULL,
    is_landlocked BOOLEAN NOT NULL,
    landlocked_justification TEXT,
    landlocked_approved_by UUID REFERENCES users(id),
    landlocked_approved_at TIMESTAMP WITH TIME ZONE,
    road_width DECIMAL(10, 2),
    road_width_unit VARCHAR(10) DEFAULT 'feet',
    road_type VARCHAR(50),
    transport_modes JSONB,
    single_or_multiple_buildings VARCHAR(20),
    number_of_buildings INTEGER,
    building_details JSONB,
    total_flats_in_complex INTEGER,
    total_towers INTEGER,
    tower_name VARCHAR(100),
    flat_type VARCHAR(50),
    uds_value DECIMAL(10, 2),
    uds_unit VARCHAR(20) DEFAULT 'sqyds',
    carpet_area DECIMAL(10, 2),
    carpet_area_unit VARCHAR(20) DEFAULT 'sqft',
    parking_area DECIMAL(10, 2),
    parking_area_unit VARCHAR(20) DEFAULT 'sqft',
    number_of_parkings INTEGER,
    common_area_share DECIMAL(5, 2),
    amenities JSONB,
    ndma_compliance VARCHAR(20),
    nbc_2005_compliance VARCHAR(20),
    ht_lines_nearby BOOLEAN,
    ht_line_distance DECIMAL(10, 2),
    ht_line_distance_unit VARCHAR(10) DEFAULT 'meters',
    buffer_areas BOOLEAN,
    buffer_area_description TEXT,
    buffer_area_distance DECIMAL(10, 2),
    buffer_area_distance_unit VARCHAR(10) DEFAULT 'meters',
    zone_usage VARCHAR(50),
    building_byelaws_adherence VARCHAR(20),
    approved_fsi DECIMAL(5, 2),
    actual_fsi DECIMAL(5, 2),
    plot_coverage DECIMAL(5, 2),
    permissible_plot_coverage DECIMAL(5, 2),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_property_details_property ON property_details(property_id);

CREATE TRIGGER set_updated_at_property_details
    BEFORE UPDATE ON property_details
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Boundaries table (with PostGIS spatial support)
CREATE TABLE boundaries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    property_id UUID UNIQUE NOT NULL REFERENCES properties(id) ON DELETE CASCADE,
    boundary_polygon GEOMETRY(Polygon, 4326),
    boundary_geojson JSONB,
    calculated_area_sqm DECIMAL(15, 2),
    north_boundary_doc TEXT,
    south_boundary_doc TEXT,
    east_boundary_doc TEXT,
    west_boundary_doc TEXT,
    north_boundary_actual TEXT,
    south_boundary_actual TEXT,
    east_boundary_actual TEXT,
    west_boundary_actual TEXT,
    north_dimension_doc DECIMAL(10, 2),
    south_dimension_doc DECIMAL(10, 2),
    east_dimension_doc DECIMAL(10, 2),
    west_dimension_doc DECIMAL(10, 2),
    dimension_unit_doc VARCHAR(10) DEFAULT 'feet',
    north_dimension_actual DECIMAL(10, 2),
    south_dimension_actual DECIMAL(10, 2),
    east_dimension_actual DECIMAL(10, 2),
    west_dimension_actual DECIMAL(10, 2),
    dimension_unit_actual VARCHAR(10) DEFAULT 'feet',
    measurement_method VARCHAR(50),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_boundaries_property ON boundaries(property_id);
CREATE INDEX idx_boundaries_polygon ON boundaries USING GIST (boundary_polygon);

CREATE TRIGGER set_updated_at_boundaries
    BEFORE UPDATE ON boundaries
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Valuation data table
CREATE TABLE valuation_data (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID UNIQUE NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    valuation_method VARCHAR(50) NOT NULL CHECK (valuation_method IN ('land_and_building', 'composite')),
    land_unit VARCHAR(20),
    land_area_document DECIMAL(15, 4),
    land_area_plan DECIMAL(15, 4),
    land_area_nala DECIMAL(15, 4),
    land_area_actual DECIMAL(15, 4),
    land_area_adopted DECIMAL(15, 4) NOT NULL,
    land_area_adoption_source VARCHAR(50),
    road_effected_area DECIMAL(15, 4) DEFAULT 0,
    road_proposed_width DECIMAL(10, 2),
    road_present_width DECIMAL(10, 2),
    road_setback_length DECIMAL(10, 2),
    nala_effected_area DECIMAL(15, 4) DEFAULT 0,
    nala_width DECIMAL(10, 2),
    nala_setback_distance DECIMAL(10, 2),
    splay_area DECIMAL(15, 4) DEFAULT 0,
    buffer_area DECIMAL(15, 4) DEFAULT 0,
    buffer_type VARCHAR(100),
    buffer_distance DECIMAL(10, 2),
    net_land_area DECIMAL(15, 4) NOT NULL,
    land_rate_type VARCHAR(50) NOT NULL CHECK (land_rate_type IN ('guideline', 'market')),
    land_rate_source VARCHAR(50),
    land_rate_per_unit DECIMAL(15, 2) NOT NULL,
    land_rate_effective_date DATE,
    land_rate_justification TEXT,
    comparable_sales_reference TEXT,
    adopted_land_rate DECIMAL(15, 2) NOT NULL,
    land_value DECIMAL(15, 2) NOT NULL,
    bua_unit VARCHAR(20),
    sbua_as_per_plan DECIMAL(15, 2),
    sbua_as_per_actual DECIMAL(15, 2),
    permissible_safe_sbua DECIMAL(15, 2),
    adopted_sbua DECIMAL(15, 2),
    sbua_adoption_source VARCHAR(50),
    total_bua_plan DECIMAL(15, 2),
    total_bua_actual DECIMAL(15, 2),
    adopted_total_bua DECIMAL(15, 2),
    flat_uds DECIMAL(15, 2),
    flat_carpet_area DECIMAL(15, 2),
    flat_parking_area DECIMAL(15, 2),
    building_rate_basis VARCHAR(50),
    building_rate_per_unit DECIMAL(15, 2),
    building_age INTEGER,
    depreciation_method VARCHAR(50),
    depreciation_percentage DECIMAL(5, 2),
    depreciated_building_rate DECIMAL(15, 2),
    gross_building_value DECIMAL(15, 2),
    depreciation_amount DECIMAL(15, 2),
    net_building_value DECIMAL(15, 2),
    composite_uds_value DECIMAL(15, 2),
    composite_rate_per_unit DECIMAL(15, 2),
    composite_rate_basis TEXT,
    composite_value DECIMAL(15, 2),
    total_property_value DECIMAL(15, 2) NOT NULL,
    distress_sale_value DECIMAL(15, 2),
    distress_sale_percentage DECIMAL(5, 2) DEFAULT 75,
    forced_sale_value DECIMAL(15, 2),
    forced_sale_percentage DECIMAL(5, 2) DEFAULT 55,
    realizable_value DECIMAL(15, 2),
    realizable_percentage DECIMAL(5, 2) DEFAULT 90,
    valuation_remarks TEXT,
    market_conditions TEXT,
    comparable_sales_data TEXT,
    recommendations TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT positive_values CHECK (
        land_value >= 0 AND
        total_property_value >= 0 AND
        net_land_area > 0
    )
);

CREATE INDEX idx_valuation_data_valuation ON valuation_data(valuation_id);

CREATE TRIGGER set_updated_at_valuation_data
    BEFORE UPDATE ON valuation_data
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- SECTION 6: SUPPORTING DATA TABLES
-- =====================================================

-- Documents table
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    document_type_id UUID NOT NULL REFERENCES document_types(id),
    parent_document_id UUID REFERENCES documents(id),
    document_name VARCHAR(500) NOT NULL,
    document_description TEXT,
    document_date DATE,
    document_number VARCHAR(200),
    file_name VARCHAR(500) NOT NULL,
    file_path TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    file_type VARCHAR(100) NOT NULL,
    thumbnail_path TEXT,
    page_count INTEGER,
    version_number INTEGER DEFAULT 1,
    version_notes TEXT,
    is_current_version BOOLEAN DEFAULT true,
    status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'reviewed', 'approved', 'rejected')),
    reviewed_by UUID REFERENCES users(id),
    reviewed_at TIMESTAMP WITH TIME ZONE,
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    annotations JSONB,
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_documents_valuation ON documents(valuation_id);
CREATE INDEX idx_documents_type ON documents(document_type_id);
CREATE INDEX idx_documents_version ON documents(parent_document_id, version_number);
CREATE INDEX idx_documents_uploaded_by ON documents(uploaded_by);
CREATE INDEX idx_documents_deleted ON documents(deleted_at) WHERE deleted_at IS NULL;

-- Photos table
CREATE TABLE photos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    category VARCHAR(50) CHECK (category IN ('front_view', 'side_view', 'interior', 'boundaries', 'road', 'neighborhood', 'defects', 'other')),
    caption TEXT,
    file_name VARCHAR(500) NOT NULL,
    file_path TEXT NOT NULL,
    thumbnail_path TEXT NOT NULL,
    file_size BIGINT NOT NULL,
    width INTEGER,
    height INTEGER,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    gps_accuracy INTEGER,
    exif_data JSONB,
    annotations JSONB,
    is_primary BOOLEAN DEFAULT false,
    display_order INTEGER DEFAULT 0,
    captured_at TIMESTAMP WITH TIME ZONE,
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE,
    status VARCHAR(20) DEFAULT 'active'
);

CREATE INDEX idx_photos_valuation ON photos(valuation_id);
CREATE INDEX idx_photos_category ON photos(category);
CREATE INDEX idx_photos_uploaded_by ON photos(uploaded_by);
CREATE INDEX idx_photos_deleted ON photos(deleted_at) WHERE deleted_at IS NULL;

-- Comments table (threaded discussions)
CREATE TABLE comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    valuation_id UUID NOT NULL REFERENCES valuations(id) ON DELETE CASCADE,
    parent_comment_id UUID REFERENCES comments(id),
    comment_text TEXT NOT NULL,
    comment_type VARCHAR(50) DEFAULT 'general' CHECK (comment_type IN ('general', 'query', 'response', 'note', 'review_note')),
    target_field VARCHAR(200),
    target_section VARCHAR(100),
    priority VARCHAR(20),
    status VARCHAR(50) DEFAULT 'open' CHECK (status IN ('open', 'resolved', 'closed')),
    resolved_by UUID REFERENCES users(id),
    resolved_at TIMESTAMP WITH TIME ZONE,
    attachments JSONB,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_comments_valuation ON comments(valuation_id);
CREATE INDEX idx_comments_parent ON comments(parent_comment_id);
CREATE INDEX idx_comments_created_by ON comments(created_by);
CREATE INDEX idx_comments_deleted ON comments(deleted_at) WHERE deleted_at IS NULL;

CREATE TRIGGER set_updated_at_comments
    BEFORE UPDATE ON comments
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- =====================================================
-- SECTION 7: AUDIT & LOGGING TABLES
-- =====================================================

-- Activity logs table
CREATE TABLE activity_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id),
    valuation_id UUID REFERENCES valuations(id),
    action_type VARCHAR(100) NOT NULL,
    action_description TEXT NOT NULL,
    entity_type VARCHAR(100),
    entity_id UUID,
    ip_address VARCHAR(50),
    user_agent TEXT,
    request_method VARCHAR(10),
    request_url TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activity_logs_user ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_valuation ON activity_logs(valuation_id);
CREATE INDEX idx_activity_logs_created ON activity_logs(created_at DESC);
CREATE INDEX idx_activity_logs_action ON activity_logs(action_type);

-- Audit logs table (data change tracking)
CREATE TABLE audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES users(id),
    valuation_id UUID REFERENCES valuations(id),
    table_name VARCHAR(100) NOT NULL,
    record_id UUID NOT NULL,
    operation VARCHAR(20) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    old_value JSONB,
    new_value JSONB,
    changed_fields TEXT[],
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX idx_audit_logs_valuation ON audit_logs(valuation_id);
CREATE INDEX idx_audit_logs_table ON audit_logs(table_name, record_id);
CREATE INDEX idx_audit_logs_created ON audit_logs(created_at DESC);

-- =====================================================
-- SECTION 8: APPLY AUDIT TRIGGERS
-- =====================================================

-- Apply audit trigger to core tables
CREATE TRIGGER audit_valuations
    AFTER INSERT OR UPDATE OR DELETE ON valuations
    FOR EACH ROW
    EXECUTE FUNCTION log_data_changes();

CREATE TRIGGER audit_properties
    AFTER INSERT OR UPDATE OR DELETE ON properties
    FOR EACH ROW
    EXECUTE FUNCTION log_data_changes();

CREATE TRIGGER audit_valuation_data
    AFTER INSERT OR UPDATE OR DELETE ON valuation_data
    FOR EACH ROW
    EXECUTE FUNCTION log_data_changes();

-- =====================================================
-- SECTION 9: SEED DATA (Basic Reference Data)
-- =====================================================

-- Insert default roles
INSERT INTO roles (role_name, role_code, role_description, permissions, role_level) VALUES
('Admin', 'ADMIN', 'System Administrator with full access', '{"valuations": {"view": true, "create": true, "edit": true, "delete": true, "approve": true}, "admin": {"view": true, "edit": true}}', 100),
('Team Lead', 'TEAM_LEAD', 'Team Lead with reporting and management access', '{"valuations": {"view": true, "create": true, "edit": true, "delete": false, "approve": false}, "reports": {"view": true, "export": true}}', 80),
('Approver', 'APPROVER', 'Final approval authority', '{"valuations": {"view": true, "create": false, "edit": false, "delete": false, "approve": true}}', 70),
('Reviewer', 'REVIEWER', 'Review and quality check', '{"valuations": {"view": true, "create": false, "edit": false, "delete": false, "review": true}}', 60),
('Key-in', 'KEYIN', 'Data entry specialist', '{"valuations": {"view": true, "create": false, "edit": true, "delete": false}}', 40),
('Surveyor', 'SURVEYOR', 'Field surveyor', '{"valuations": {"view": true, "create": false, "edit": true, "delete": false}, "documents": {"view": true, "create": true, "edit": true}}', 50),
('Creator', 'CREATOR', 'Valuation creator', '{"valuations": {"view": true, "create": true, "edit": true, "delete": false}}', 30);

-- Insert Telangana location hierarchy (sample data)
-- State
INSERT INTO locations (location_type, location_name, location_code, status) VALUES
('state', 'Telangana', 'TS', 'active');

-- Districts (sample)
INSERT INTO locations (location_type, parent_id, location_name, location_code, status)
SELECT 'district', id, 'Hyderabad', 'HYD', 'active' FROM locations WHERE location_code = 'TS'
UNION ALL
SELECT 'district', id, 'Rangareddy', 'RR', 'active' FROM locations WHERE location_code = 'TS'
UNION ALL
SELECT 'district', id, 'Medchal-Malkajgiri', 'MM', 'active' FROM locations WHERE location_code = 'TS';

-- Insert sample banks
INSERT INTO banks (bank_name, bank_code, sla_days, status) VALUES
('State Bank of India', 'SBI', 5, 'active'),
('HDFC Bank', 'HDFC', 5, 'active'),
('ICICI Bank', 'ICICI', 5, 'active'),
('Axis Bank', 'AXIS', 5, 'active'),
('Kotak Mahindra Bank', 'KOTAK', 5, 'active'),
('Bank of Baroda', 'BOB', 5, 'active');

-- Insert common document types
INSERT INTO document_types (document_type_name, document_category, is_mandatory, status) VALUES
('Sale Deed', 'legal', true, 'active'),
('Encumbrance Certificate', 'legal', true, 'active'),
('Tax Receipts', 'financial', true, 'active'),
('Possession Certificate', 'legal', false, 'active'),
('Building Plan Approval', 'technical', false, 'active'),
('Occupancy Certificate', 'technical', false, 'active'),
('Property Card', 'legal', false, 'active'),
('Bank Passbook', 'financial', false, 'active');

-- Insert common lookup values
INSERT INTO lookup_values (category, value, label, status) VALUES
-- Shape of Site
('shape_of_site', 'regular', 'Regular', 'active'),
('shape_of_site', 'irregular', 'Irregular', 'active'),
('shape_of_site', 'rectangular', 'Rectangular', 'active'),
('shape_of_site', 'square', 'Square', 'active'),
('shape_of_site', 'l_shaped', 'L-Shaped', 'active'),
('shape_of_site', 'trapezoidal', 'Trapezoidal', 'active'),

-- Level
('level', 'plain', 'Plain', 'active'),
('level', 'undulated', 'Undulated', 'active'),
('level', 'sloped', 'Sloped', 'active'),

-- Development Status
('development_status', 'fully_developed', 'Fully Developed', 'active'),
('development_status', 'under_development', 'Under Development', 'active'),
('development_status', 'undeveloped', 'Undeveloped', 'active'),

-- Infrastructure Quality
('infrastructure_quality', 'excellent', 'Excellent', 'active'),
('infrastructure_quality', 'good', 'Good', 'active'),
('infrastructure_quality', 'average', 'Average', 'active'),
('infrastructure_quality', 'poor', 'Poor', 'active'),

-- Road Type
('road_type', 'paved', 'Paved', 'active'),
('road_type', 'unpaved', 'Unpaved', 'active'),
('road_type', 'cement', 'Cement', 'active'),
('road_type', 'mud', 'Mud', 'active'),
('road_type', 'gravel', 'Gravel', 'active');

-- =====================================================
-- SECTION 10: VIEWS (Optional - for common queries)
-- =====================================================

-- View: Complete valuation details with all related data
CREATE OR REPLACE VIEW vw_valuations_complete AS
SELECT 
    v.id AS valuation_id,
    v.valuation_number,
    v.status,
    v.priority,
    v.created_at,
    b.bank_name,
    b.bank_code,
    p.address AS property_address,
    p.pincode,
    l_state.location_name AS state,
    l_district.location_name AS district,
    a.applicant_name,
    a.contact_mobile,
    u_creator.first_name || ' ' || u_creator.last_name AS created_by_name,
    u_surveyor.first_name || ' ' || u_surveyor.last_name AS surveyor_name,
    u_reviewer.first_name || ' ' || u_reviewer.last_name AS reviewer_name,
    vd.total_property_value,
    vd.realizable_value
FROM valuations v
LEFT JOIN banks b ON v.bank_id = b.id
LEFT JOIN properties p ON v.id = p.valuation_id
LEFT JOIN locations l_state ON p.state_id = l_state.id
LEFT JOIN locations l_district ON p.district_id = l_district.id
LEFT JOIN applicants a ON v.id = a.valuation_id
LEFT JOIN users u_creator ON v.created_by = u_creator.id
LEFT JOIN users u_surveyor ON v.assigned_surveyor = u_surveyor.id
LEFT JOIN users u_reviewer ON v.assigned_reviewer = u_reviewer.id
LEFT JOIN valuation_data vd ON v.id = vd.valuation_id
WHERE v.deleted_at IS NULL;

-- View: User dashboard metrics
CREATE OR REPLACE VIEW vw_user_dashboard_metrics AS
SELECT 
    assigned_surveyor AS user_id,
    COUNT(*) FILTER (WHERE status = 'draft') AS draft_count,
    COUNT(*) FILTER (WHERE status = 'survey') AS survey_count,
    COUNT(*) FILTER (WHERE status = 'submitted') AS submitted_count,
    COUNT(*) FILTER (WHERE status = 'review') AS review_count,
    COUNT(*) FILTER (WHERE status = 'approved') AS approved_count,
    COUNT(*) AS total_count
FROM valuations
WHERE deleted_at IS NULL
GROUP BY assigned_surveyor;

-- =====================================================
-- END OF SCHEMA
-- =====================================================

-- Schema version tracking (optional)
CREATE TABLE IF NOT EXISTS schema_version (
    version VARCHAR(20) PRIMARY KEY,
    applied_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    description TEXT
);

INSERT INTO schema_version (version, description) VALUES
('1.0.0', 'Initial schema with all 28 tables, relationships, indexes, triggers, and seed data');

-- =====================================================
-- NOTES
-- =====================================================
-- 1. Remember to set PostgreSQL session variable before operations:
--    SET app.current_user_id = 'your-user-uuid-here';
--
-- 2. Ensure PostGIS extension is installed for spatial features
--
-- 3. All UUIDs are auto-generated using gen_random_uuid()
--
-- 4. Soft delete is implemented via deleted_at timestamp
--
-- 5. All tables have created_at and updated_at timestamps
--
-- 6. Audit logging is automatic for core tables via triggers
--
-- 7. For production deployment:
--    - Review and adjust SLA days per bank
--    - Configure proper backup strategy
--    - Set up connection pooling (PgBouncer)
--    - Enable query logging for performance monitoring
--    - Configure appropriate access controls and roles
-- =====================================================