Real estate

// Lookup Table for Venture Groups
Table venture_groups {
    id integer [primary key]
    name varchar [unique]  // "Residential", "Commercial", etc.
}

// Main Venture Table
Table ventures {
    id integer [primary key]
    venture_name varchar
    address text
    launched_on date
    corpus decimal(15, 2)
    lp_no varchar
    group_id integer
    cut_off_commission boolean
}

// Lookup Table for Amenities (Many-to-many relationship)
Table amenities {
    id integer [primary key]
    name varchar [unique]  // "Pool", "Gym", "Parking", etc.
}

// Join Table for Ventures and Amenities
Table venture_amenities {
    venture_id integer
    amenity_id integer
    primary key (venture_id, amenity_id)
}

// Relationships
Ref ventures_groups: ventures.group_id > venture_groups.id // Many-to-one
Ref venture_amenities_venture: venture_amenities.venture_id > ventures.id
Ref venture_amenities_amenity: venture_amenities.amenity_id > amenities.id

// Lookup Table for Hierarchy Schemes
Table hierarchy_schemes {
    id integer [primary key]
    name varchar [unique] // e.g., "Commission Scheme", "Promotion Scheme"
}

// Lookup Table for Hierarchy Designations
Table hierarchy_designations {
    id integer [primary key]
    name varchar [unique] // e.g., "Manager", "Executive", "Trainee"
}

// Main Hierarchy Table
Table hierarchies {
    id integer [primary key]
    date date
    scheme_id integer
    designation_id integer
    level integer
}

// Relationships
Ref hierarchies_schemes: hierarchies.scheme_id > hierarchy_schemes.id // Many-to-one
Ref hierarchies_designations: hierarchies.designation_id > hierarchy_designations.id // Many-to-one

// Lookup Table for Agent Codes
Table agent_codes {
    id integer [primary key]
    code varchar [unique]  // e.g., "AGT001", "AGT002"
}

// Lookup Table for Designations (Reusing from Hierarchy, assuming it's the same)
// Table hierarchy_designations {} // Already defined above

// Main Agent Hierarchy Table
Table agent_hierarchies {
    id integer [primary key]
    date date
    agent_code_id integer  // References agent_codes
    name varchar
    parent_agent_code_id integer  // References agent_codes, allows NULL for top-level agents
    parent_name varchar  // Could be denormalized for easier querying, allows NULL
     // Denormalized columns from the second "Hierarchy Table" section:
    hierarchy_table_date DATE
    hierarchy_table_agent_code VARCHAR(255)
    hierarchy_table_agent_name VARCHAR(255)
    hierarchy_table_designation VARCHAR(255)
    hierarchy_table_parent_code VARCHAR(255)
    hierarchy_table_parent_name VARCHAR(255)
    hierarchy_table_designation_parent VARCHAR(255)
}

// Relationships
Ref agent_hierarchies_agent_codes: agent_hierarchies.agent_code_id > agent_codes.id
Ref agent_hierarchies_parent_agent_codes: agent_hierarchies.parent_agent_code_id > agent_codes.id


// Lookup Table for Towns/Cities
Table towns_cities {
    id integer [primary key]
    name varchar [unique]
}

// Lookup Table for States
Table states {
    id integer [primary key]
    name varchar [unique]
}

// Lookup table for relations
Table relations {
    id integer [primary key]
    name varchar [unique]
}

// Lookup Table for Facings (for plots)
Table facings {
    id integer [primary key]
    name varchar [unique]  // "North", "South", "East", "West", etc.
}

// Lookup Table for Teams (for commission details)
Table teams {
    id integer [primary key]
    name varchar [unique]
}
// Customer Booked by lookup table
Table customer_booked_by {
	id integer [primary key]
	name varchar [unique]
}

// Main Customer Table
Table customers {
    id integer [primary key]  // Renamed from S.No
    date date
    pass_book_no varchar
    app_ref_no varchar
    cust_id varchar
    cust_name varchar

    // Contact Details
    aadhar_no varchar(12)
    mobile varchar(10)
    mobile_2 varchar(10)
    email varchar

    // Nominee Details
    nominee varchar
    relation_id integer
    nominee_age integer

    // Residential Address
    res_h_no_flat_no varchar
    res_street varchar
    res_area varchar
    res_town_city_id integer
    res_pin integer(6)
    res_state_id integer

    // Office Address
    off_flat_no varchar
    off_street varchar
    off_town_city_id integer
    off_pin integer(6)
    off_state_id integer

    // Other Personal Details
    occupation varchar
    dob date
    age integer
    dom date
    father_name varchar
    guardian_name varchar
    husband_name varchar
    remarks text
    // Commission Details, moved attributes
	team_id integer
	booked_by_id integer
	direct boolean
}

// Plot Details (This is now a separate table, linked to Customer)
Table customer_plots {
    id integer [primary key]
    customer_id integer
    s_no integer  // Auto-incrementing within each customer
    site_name varchar  // Could be a foreign key to a sites table
    plot_no varchar
    facing_id integer
    area decimal(10, 2)
    rate decimal(10, 2)
    e_rate decimal(10, 2)
    amount decimal(15, 2)
    discount decimal(15, 2)
    others decimal(15, 2)
    total_plot_cost decimal(15, 2)
    cancel boolean
}
// Relationships
Ref customers_towns_cities_res: customers.res_town_city_id > towns_cities.id
Ref customers_states_res: customers.res_state_id > states.id
Ref customers_towns_cities_off: customers.off_town_city_id > towns_cities.id
Ref customers_states_off: customers.off_state_id > states.id
Ref customer_plots_customers: customer_plots.customer_id > customers.id
Ref customer_plots_facings: customer_plots.facing_id > facings.id
Ref customers_relations : customers.relation_id > relations.id
Ref customers_teams : customers.team_id > teams.id
Ref customers_booked_by : customers.booked_by_id > customer_booked_by.id


// Lookup Table for Sites (Assuming this is needed for Receipts)
Table sites {
    id integer [primary key]
    name varchar [unique]
}

// Lookup Table for Collection Agents
Table collection_agents {
    id integer [primary key]
    name varchar [unique]
}

// Lookup Table for Cash/Bank Accounts (Debit)
Table cash_bank_accounts {
    id integer [primary key]
    name varchar [unique]  // e.g., "Cash", "Bank Account 1", etc.
}

// Lookup Table for Accounts (Credit)
Table accounts {
    id integer [primary key]
    name varchar [unique]  // e.g., "Sales Revenue", "Customer Advance", etc.
}
//Lookup table for Nature of Payment
Table nature_of_payment {
	id integer [primary key]
	name varchar [unique]
}
// Main Receipt Table
Table receipts {
    id integer [primary key]
    receipt_no varchar [unique]  // Unique identifier
    site_id integer
    date date
    coll_agent_id integer
    reference varchar
    load_all_accounts boolean  // Checkbox

    // Denormalized transaction details for easier querying:
    cash_bank_dr varchar   // Could link to cash_bank_accounts.name
    account_cr varchar      // Could link to accounts.name
    narration varchar
    nature_of_payment_id integer
    amount decimal(15, 2)
    cheque_no varchar
    pd_date date
    name varchar
    cc_ot varchar
    emp_no varchar
    jv_no varchar
}
// Relationships
Ref receipts_sites: receipts.site_id > sites.id
Ref receipts_collection_agents: receipts.coll_agent_id > collection_agents.id
Ref receipts_nature_of_payment: receipts.nature_of_payment_id > nature_of_payment.id


// Reusing the 'sites' and 'facings' tables from previous sections

// Lookup table for plot types
Table plot_types {
    id integer [primary key]
    name varchar [unique]  // e.g., "Residential", "Commercial"
}

// Main Plot Table
Table plots {
    id integer [primary key]
    site_id integer
    actual_plot_no varchar
    plot_no varchar
    facing_id integer
    area decimal(10, 2)
    actual_rate decimal(10, 2)
    extra_rate decimal(10, 2)
    points integer
    amount decimal(15, 2)
    amenities varchar  //  This could be improved, see notes below
    discount_amount decimal(15, 2)
    width decimal(10, 2)
    length decimal(10, 2)
    allotment boolean
    hold boolean
    part boolean
    reg_rate decimal(10, 2)
    reg_amount decimal(15, 2)
    app_fee_per_unit decimal(10, 2)
     //  'Amount' is listed twice; I've included only one.
    total_plot_cost decimal(15, 2)
    type_id integer
}

// Relationships
Ref plots_sites: plots.site_id > sites.id
Ref plots_facings: plots.facing_id > facings.id
Ref plots_types: plots.type_id > plot_types.id

// Reusing 'sites' table from previous definitions

// Main Block Table
Table blocks {
    id integer [primary key]
    site_id integer
    flat_plot varchar  // Assuming this is a descriptive string, e.g., "Flat A-101", "Plot 5"
    customer varchar  // Consider making this a foreign key to the 'customers' table
    mobile varchar
}

// Relationships
Ref blocks_sites: blocks.site_id > sites.id


// Lookup Table for Teams (Assuming this is different from the previous 'teams' table)

// Lookup Table for Statuses
Table agent_statuses {
    id integer [primary key]
    name varchar [unique]  // "Active", "Inactive", "On Leave", etc.
}

// Lookup Table for Departments
Table departments {
    id integer [primary key]
    name varchar [unique]
}

// Lookup Table for Companies
Table companies {
	id integer [primary key]
	name varchar [unique]
}

// Main Agent Table
Table agents {
    id integer [primary key]
    emp_id varchar
    emp_name varchar
    team_name_id integer
    aadhar_no varchar(12)
    status_id integer
    dob date
    age integer
    doj date
    blood_group varchar
    gender boolean  // True for Male, False for Female (or use an enum)
    marital_status boolean  // True for Married, False for Single (or use an enum)
    qualification varchar
    experience integer
    company_id integer
    salary decimal(10, 2)
    designation varchar
    department_id integer
    bank_name varchar
    branch varchar
    account_no varchar
    ifsc_code varchar
    father_name varchar
    //Nominee Details
    nominee_name varchar
    nominee_address text
    nominee_age integer
    nominee_relationship varchar
    pan_no varchar
    phone_no varchar
    land_line varchar
    mobile varchar
    email varchar
    present_address text
    permanent_address text
    scheme varchar
    esi_pf_tds boolean
    hobbies text
}

// Relationships
Ref agents_teams: agents.team_name_id > teams.id  // Assuming a separate 'teams' table
Ref agents_statuses: agents.status_id > agent_statuses.id
Ref agents_departments: agents.department_id > departments.id
Ref agents_companies: agents.company_id > companies.id



// Reusing 'ventures' table

// Main Approval for Registration Table
Table approval_for_registrations {
    id integer [primary key]
    approval_no varchar
    date date
    customer_id varchar  // Could be a foreign key to customers
    customer_name varchar
    venture_id integer
    lp_no varchar
    plot_no varchar
    area decimal(10, 2)
    final_rate decimal(10, 2)

    // Denormalized Customer Information
    cust_info_name varchar
    cust_info_age integer
    cust_info_s_d_w_of varchar
    cust_info_occupation varchar
    cust_info_door_no varchar
    cust_info_street_or_village varchar
    cust_info_town_city varchar
    cust_info_pin_code integer

    // Denormalized Details of Registration on Whom
    registered_to varchar
    reg_age integer
    reg_s_d_w_of varchar
    reg_occupation varchar
    reg_door_no varchar
    reg_street_or_village varchar
    reg_town_city varchar
    reg_pin_code integer

    // Details of Payment
    total_plot_cost decimal(15, 2)
    less_discount decimal(15, 2)
    net_plot_cost decimal(15, 2)
    registration_expenses decimal(15, 2)
    total_payable decimal(15, 2)
    paid_as_on date
    net_balance_due decimal(15, 2)
    remarks text
}

// Relationships
Ref approval_for_registrations_ventures: approval_for_registrations.venture_id > ventures.id


// Main Allotment Table
Table allotments {
    id integer [primary key]
    customer_id varchar // Foreign Key to customers table if consistent
    name varchar
    received_amount decimal(15,2)
    serial_no integer
    site_name varchar // Foreign Key to sites table if consistent
    plot_no varchar
    area decimal(10, 2)
    facing_id integer // Foreign Key
    actual_rate decimal(10,2)
    total_plot_cost decimal(15,2)
    allotment_amount decimal(15,2)
    select boolean
    date date
}

// Relationships
Ref allotments_customer: allotments.customer_id > customers.cust_id
Ref allotments_facings: allotments.facing_id > facings.id
Ref allotments_sites: allotments.site_name> sites.name



// Lookup table for Paid To
Table payment_paid_to {
    id integer [primary key]
    name varchar [unique]
}

// Lookup table for Approved By
Table payment_approved_by {
    id integer [primary key]
    name varchar [unique]
}

// Main Payment Table
Table payments {
    id integer [primary key]
    voucher_no varchar [unique]
    date date
    site_name_id integer // Foreign Key
    paid_to_id integer // Foreign Key
    approved_by_id integer // Foreign Key
    reference varchar
    pan_no varchar
    cash_bank_cr_id integer // Foreign Key
    account_dr_id integer  // Foreign Key
    narration varchar
    bill_amount decimal(15,2)
    tds_percentage decimal(5,2)
    tds_amount decimal(15,2)
    net_amount decimal(15,2)
    cheque_no varchar
    p_d boolean
    payment_date date
    payee_name varchar
    w_o_no varchar
    emp_no varchar
    update_dr_head boolean
     // Totals are calculated, not stored
}

// Relationships
Ref payments_sites: payments.site_name_id > sites.id
Ref payments_paid_to: payments.paid_to_id > payment_paid_to.id
Ref payments_approved_by: payments.approved_by_id > payment_approved_by.id
Ref payments_cash_bank_cr: payments.cash_bank_cr_id > cash_bank_accounts.id
Ref payments_account_dr: payments.account_dr_id > accounts.id


// Main Cancellation Table
Table cancellations {
    id integer [primary key]
    customer_id varchar // Foreign Key
    name varchar
    serial_no integer
    site_name varchar  //FK
    plot_no varchar
    area decimal(10, 2)
    facing_id integer // Foreign Key
    actual_rate decimal(10, 2)
    amount decimal(15, 2)
    total_plot_cost decimal(15, 2)
    cancel boolean
    date date
		//Commissions & Gifts
		commissions_date date
    particulars varchar
    commissions_amount decimal(15, 2)
		//Total calculation will calculated
}

// Relationships
Ref cancellations_customer: cancellations.customer_id > customers.cust_id
Ref cancellations_facings: cancellations.facing_id > facings.id
Ref cancellations_sites: cancellations.site_name > sites.name


// Lookup for approval no
Table registration_approval_nos{
	id integer [primary key]
	approval_no varchar [unique]
}

// Main Registration Table
Table registrations {
    id integer [primary key]
    approval_no_id integer // Foreign Key
    date date
    site_name varchar
    plot_flat_no varchar
    registered_to varchar
    address varchar
    customer_id varchar
    customer_name varchar
    recd_amt decimal(15, 2)
    area decimal(10, 2)
    rate decimal(10, 2)
    amount decimal(15, 2)
    facing varchar
    extra_rate decimal(10, 2)
    extra_amount decimal(15, 2)
    discount decimal(15, 2)
    dimensions varchar
    amenities varchar
    registration_rate decimal(10, 2)
    registration_amount decimal(15, 2)
    plot_cost decimal(15, 2)
    ag_rate decimal(10, 2)
    ag_amount decimal(15, 2)
    total_cost_with_registration decimal(15, 2)
    feedback_section text
    challan_no varchar
    challan_date date
    challan_amount decimal(15, 2)
    ec_amount decimal(15, 2)
}

// Relationships
Ref registrations_approval_nos: registrations.approval_no_id > registration_approval_nos.id
Ref registrations_sites: registrations.site_name > sites.name