File: /home/durgeshpandey215/public_html/bharatmatabackend.skilladders.com/DB/raw.sql
CREATE TABLE `users` (
`id` bigint(20) UNSIGNED NOT NULL,
`role_id` bigint(20) UNSIGNED DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`phone` varchar(15) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`otp` varchar(10) DEFAULT NULL,
`otp_expires_at` datetime DEFAULT NULL,
`is_kyc_completed` tinyint(1) DEFAULT 0,
`is_active` tinyint(1) DEFAULT 1,
`remember_token` varchar(100) DEFAULT NULL,
`phone_verified_at` timestamp(1) NULL DEFAULT NULL,
`email_verified_at` timestamp NULL DEFAULT NULL,
`created_by` bigint(20) UNSIGNED DEFAULT NULL,
`updated_by` bigint(20) UNSIGNED DEFAULT NULL,
`deleted_by` bigint(20) UNSIGNED DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
reate table zones (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
state VARCHAR(100) NOT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL
) ENGINE = InnoDB;
create table zonal_areas (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
zone_id BIGINT UNSIGNED NOT NULL,
pincode VARCHAR(20) NOT NULL,
name VARCHAR(255) NOT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_zone_id (zone_id),
CONSTRAINT fk_zonal_area_zone FOREIGN KEY (zone_id) REFERENCES zones(id) ON DELETE CASCADE
) ENGINE = InnoDB;
create table branches (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
zonal_area_id BIGINT UNSIGNED NOT NULL COMMENT 'Main branch area',
area_covered JSON NULL COMMENT '["1", "2", "3"]',
address_line1 VARCHAR(255) NOT NULL,
address_line2 VARCHAR(255) NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
pincode VARCHAR(20) NOT NULL,
latitude DECIMAL(10, 8) NULL,
longitude DECIMAL(11, 8) NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL
) ENGINE = InnoDB;
CREATE TABLE categories (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(150) NOT NULL,
slug VARCHAR(150) NOT NULL UNIQUE,
parent_id BIGINT UNSIGNED NULL,
icon VARCHAR(255) NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_parent_id (parent_id),
INDEX idx_level (level),
CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE = InnoDB;
-- //12 Feb 2026: Adding service_category table to link services with categories and support additional metadata specific to services.
Create table service_category(
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_category_id (category_id),
INDEX idx_name (name),
INDEX idx_slug (slug),
CONSTRAINT fk_service_category_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
)
-- // 12 Feb 2026: Adding business_types table to define different types of businesses and their characteristics, which can be linked to business registrations for better categorization and management.
CREATE TABLE business_types (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL UNIQUE,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_name (name),
INDEX idx_slug (slug)
) ENGINE = InnoDB;
-- // 12 Feb 2026: Inserting predefined business types to provide a standardized set of options for categorizing businesses during registration and management.
INSERT INTO business_types (name, slug, description) VALUES
('Individual', 'individual', 'A single person conducting business activities without forming a separate legal entity.'),
('Sole Proprietorship', 'sole-proprietorship', 'A business owned and operated by a single individual.'),
('Partnership', 'partnership', 'A business owned by two or more individuals who share profits and liabilities.'),
('Limited Liability Company (LLC)', 'limited-liability-company', 'A hybrid business structure that offers limited liability to its owners.'),
('Private Limited Company', 'private-limited-company', 'A company that is privately held and has limited liability for its shareholders.'),
('Corporation', 'corporation', 'A legal entity that is separate from its owners, providing limited liability and potential tax benefits.'),
('Cooperative', 'cooperative', 'A business owned and operated by a group of individuals for their mutual benefit.');
-- // 12 Feb 2026: Modify category_groups table to support grouping of categories for better organization and management, allowing for more flexible categorization of services and businesses.
CREATE TABLE category_groups (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_ids JSON NULL COMMENT '["1", "2", "3"]',
code VARCHAR(20) NOT NULL UNIQUE COMMENT 'G1, G2, G3...',
name VARCHAR(255) NOT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_code (code),
INDEX idx_name (name)
) ENGINE = InnoDB;
-- // 12 Feb 2026: Removing category_group_mappings table and replacing it with a more flexible JSON-based approach in the category_groups table to manage category associations, simplifying the schema and improving performance for category grouping operations.
-- CREATE TABLE category_group_mappings (
-- id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
-- category_group_id BIGINT UNSIGNED NOT NULL,
-- category_id BIGINT UNSIGNED NOT NULL,
-- is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
-- created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
-- updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-- deleted_at TIMESTAMP NULL DEFAULT NULL,
-- UNIQUE KEY uniq_category_group (category_group_id, category_id),
-- INDEX idx_group_id (category_group_id),
-- INDEX idx_category_id (category_id),
-- CONSTRAINT fk_cgm_group FOREIGN KEY (category_group_id) REFERENCES category_groups(id) ON DELETE CASCADE,
-- CONSTRAINT fk_cgm_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
-- ) ENGINE = InnoDB;
CREATE TABLE status_enum (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
status_type VARCHAR(100) NOT NULL COMMENT 'e.g., category_group_status, business_registration_status',
description JSON NULL COMMENT '{"id": 1, "name": "Active", "code": "active", "color": "#00FF00"}',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_type (status_type),
UNIQUE KEY uniq_type_code (status_type)
) ENGINE = InnoDB;
CREATE TABLE required_documents (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_group_id BIGINT UNSIGNED NOT NULL,
description JSON NULL COMMENT '{"id": 1, "name": "Aadhar Card", "code": "aadhar_card", "is_mandatory": true, "document_type": ["pdf", "image"], "validity_period": "1 year"}',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_category_group_id (category_group_id),
CONSTRAINT fk_rd_category_group FOREIGN KEY (category_group_id) REFERENCES category_groups(id) ON DELETE CASCADE
) ENGINE = InnoDB;
-- // 12 Feb 2026: Adding subscription_plans table to manage different subscription options for businesses, allowing for flexible pricing and feature management based on the needs of different business types and sizes.
CREATE TABLE subscription_plans (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
category_group_id BIGINT UNSIGNED NOT NULL,
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) NOT NULL UNIQUE,
description TEXT NULL,
billing_type ENUM('onetime', 'recurring') NOT NULL DEFAULT 'recurring' COMMENT 'Defines if the plan is a one-time purchase or a recurring subscription',
billing_cycle ENUM('monthly', 'quarterly', 'yearly', 'custom') NOT NULL DEFAULT 'monthly' COMMENT 'Defines the billing cycle for recurring subscriptions',
base_price DECIMAL(10, 2) NOT NULL COMMENT 'The base price for the subscription plan',
setup_fee DECIMAL(10, 2) DEFAULT 0.00 COMMENT 'One-time setup fee for the subscription plan',
trail_days INT DEFAULT 0 COMMENT 'Number of trial days offered for the subscription plan',
is_custom_plan TINYINT DEFAULT 0 COMMENT '1=Custom plan with flexible billing cycle, 0=Standard plan with fixed billing cycle',
is_popular TINYINT DEFAULT 0 COMMENT '1=Popular plan, 0=Regular plan',
is_recommended TINYINT DEFAULT 0 COMMENT '1=Recommended plan, 0=Regular plan',
max_users INT DEFAULT NULL COMMENT 'Maximum number of users allowed under this subscription plan, NULL means unlimited',
max_services INT DEFAULT NULL COMMENT 'Maximum number of services allowed under this subscription plan, NULL means unlimited',
max_leads INT DEFAULT NULL COMMENT 'Maximum number of leads allowed under this subscription plan, NULL means unlimited',
sort_order INT DEFAULT 0 COMMENT 'Defines the display order of subscription plans, lower numbers are displayed first',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_slug (slug),
INDEX idx_billing_type (biulling_type),
INDEX idx_billing_cycle (billing_cycle),
INDEX idx_is_popular (is_popular),
INDEX idx_is_recommended (is_recommended)
) ENGINE = InnoDB;
-- // 12 Feb 2026: Adding features table to define various features that can be associated with subscription plans, allowing for better management of plan offerings and customization based on business needs.
create table features (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL, --//e.g., "Unlimited Projects", "Priority Support", "Custom Branding"
slug VARCHAR(255) NOT NULL UNIQUE,
value_type ENUM('boolean', 'integer', 'decimal', 'string', 'json') NOT NULL DEFAULT 'boolean' COMMENT 'Defines the type of value this feature holds',
is_core TINYINT DEFAULT 0 COMMENT '1=Core feature included in all plans, 0=Optional feature that can be added to plans',
is_addon_allowed TINYINT DEFAULT 0 COMMENT '1=This feature can be added as an addon to subscription plans, 0=This feature cannot be added as an addon',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_slug (slug),
INDEX idx_name (name)
) ENGINE = InnoDB;
-- public function store(Request $request)
-- {
-- $plan = SubscriptionPlan::create([
-- 'name' => $request->name,
-- 'base_price' => $request->base_price,
-- 'billing_type' => $request->billing_type,
-- 'billing_cycle' => $request->billing_cycle
-- ]);
-- foreach ($request->features as $feature) {
-- if (!$feature['enabled']) {
-- continue;
-- }
-- SubscriptionFeatureValue::create([
-- 'subscription_plan_id' => $plan->id,
-- 'feature_id' => $feature['feature_id'],
-- 'feature_value' => isset($feature['value'])
-- ? json_encode(['value' => $feature['value']])
-- : null,
-- 'is_unlimited' => $feature['is_unlimited'] ?? 0
-- ]);
-- }
-- return response()->json(['status'=>true]);
-- }
-- // 12 Feb 2026: Adding subscription_feature_values table to link features with subscription plans and store the specific values for each feature within a plan, allowing for flexible configuration of subscription offerings based on the defined features.
create table subscription_feature_values (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
subscription_plan_id BIGINT UNSIGNED NOT NULL,
feature_id BIGINT UNSIGNED NOT NULL,
feature_value JSON NULL COMMENT 'The value of the feature for this subscription plan, stored as JSON to accommodate different value types',
is_unlimited TINYINT DEFAULT 0 COMMENT '1=Unlimited value for this feature, 0=Value is defined in feature_value',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
UNIQUE KEY uniq_plan_feature (subscription_plan_id, feature_id),
INDEX idx_subscription_plan_id (subscription_plan_id),
INDEX idx_feature_id (feature_id),
CONSTRAINT fk_spf_subscription_plan FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
CONSTRAINT fk_spf_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE
) ENGINE = InnoDB;
-- // 12 Feb 2026: Adding subscription_addons table to manage optional features that can be added to subscription plans for an additional cost, allowing businesses to customize their subscription offerings based on specific needs and preferences.
create table subscription_addons (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
subscription_plan_id BIGINT UNSIGNED NOT NULL,
feature_id BIGINT UNSIGNED NOT NULL,
addon_price DECIMAL(10, 2) NOT NULL COMMENT 'Price for adding this feature as an addon to the subscription plan',
addon_limit INT DEFAULT NULL COMMENT 'Limit for this addon feature, NULL means unlimited',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_subscription_plan_id (subscription_plan_id),
INDEX idx_feature_id (feature_id),
CONSTRAINT fk_sa_subscription_plan FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
CONSTRAINT fk_sa_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE
) ENGINE = InnoDB;
-- // 12 Feb 2026: Adding user_subscriptions table to track which users are subscribed to which plans, along with subscription details such as start and end dates, price paid, and status, allowing for effective management of user subscriptions and billing.
create table user_subscriptions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
subscription_plan_id BIGINT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
price_paid DECIMAL(10, 2) NOT NULL,
discount_amount DECIMAL(10, 2) DEFAULT 0.00,
status BIGINT UNSIGNED DEFAULT NULL COMMENT 'status enum description id',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_by BIGINT UNSIGNED DEFAULT NULL,
updated_by BIGINT UNSIGNED DEFAULT NULL,
deleted_by BIGINT UNSIGNED DEFAULT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_user_id (user_id),
INDEX idx_subscription_plan_id (subscription_plan_id),
INDEX idx_status (status),
CONSTRAINT fk_us_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT fk_us_subscription_plan FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
CONSTRAINT fk_us_status FOREIGN KEY (status) REFERENCES status_enum(id) ON DELETE SET NULL
) ENGINE = InnoDB;
-- // 12 Feb 2026: Adding subscription_usages table to track the usage of features under each user subscription, allowing for monitoring of feature consumption and enforcing limits based on the subscription plan.
create table subscription_usages (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_subscription_id BIGINT UNSIGNED NOT NULL,
feature_id BIGINT UNSIGNED NOT NULL,
used_value INT DEFAULT 0 COMMENT 'Tracks the usage count for this feature under the user subscription',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
UNIQUE KEY uniq_subscription_feature (user_subscription_id, feature_id),
INDEX idx_user_subscription_id (user_subscription_id),
INDEX idx_feature_id (feature_id),
CONSTRAINT fk_su_user_subscription FOREIGN KEY (user_subscription_id) REFERENCES user_subscriptions(id) ON DELETE CASCADE,
CONSTRAINT fk_su_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE
) ENGINE = InnoDB;
-- //Hint: How to use these subscriptions tables refere this Link https://chatgpt.com/share/698d87a1-7520-8012-aca4-b513f5f61786
-- // 12 Feb 2026: Adding subscription_logs table to maintain a history of actions related to user subscriptions, such as creation, renewal, cancellation, and feature usage updates, allowing for better tracking and auditing of subscription activities.
create table subscription_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_subscription_id BIGINT UNSIGNED NOT NULL,
user_id BIGINT UNSIGNED NOT NULL,
action_type BIGINT UNSIGNED NOT NULL COMMENT 'e.g., subscription_created, subscription_renewed, subscription_cancelled', -- Create ENUM for this with these values(created, renewed, upgraded, downgraded, cancelled, expired, payment_failed, addon_purchased, admin_updated, feature_usage_updated)
-- User Buy the plan the action_type=created, when user renews the plan action_type=renewed, when user upgrade the plan action_type=upgraded, when user downgraded the plan action_type=downgraded, when user cancels the plan action_type=cancelled, when plan expires action_type=expired, when payment fails action_type=payment_failed, when user purchases addon action_type=addon_purchased, when admin updates the subscription details action_type=admin_updated, when feature usage is updated action_type=feature_usage_updated
old_subscription_plan_id BIGINT UNSIGNED NULL,
new_subscription_plan_id BIGINT UNSIGNED NULL,
old_price DECIMAL(10, 2) NULL,
new_price DECIMAL(10, 2) NULL,
metadata JSON NULL COMMENT 'Additional data related to the subscription action, stored as JSON for flexibility', -- e.g., {"ip_address": "192.168.1.1", "payment_id": "pay_1234567890", "transaction_id": "txn_1234", "old_end_date": "2024-12-31", "new_end_date": "2025-12-31", "upgrade_difference": 20.00, "reson": "User requested upgrade from Basic to Pro plan"}
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_user_subscription_id (user_subscription_id),
INDEX idx_user_id (user_id),
INDEX idx_action_type (action_type),
CONSTRAINT fk_sl_user_subscription FOREIGN KEY (user_subscription_id) REFERENCES user_subscriptions(id) ON DELETE CASCADE,
CONSTRAINT fk_sl_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE = InnoDB;
create table business_registration (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
category_group_id BIGINT UNSIGNED NOT NULL,
business_name VARCHAR(255) NOT NULL,
registration_number VARCHAR(100) NOT NULL UNIQUE,
registration_date DATE NOT NULL,
status BIGINT UNSIGNED DEFAULT NULL COMMENT 'status enum description id',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_by BIGINT UNSIGNED DEFAULT NULL,
updated_by BIGINT UNSIGNED DEFAULT NULL,
deleted_by BIGINT UNSIGNED DEFAULT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_user_id (user_id),
INDEX idx_category_group_id (category_group_id),
INDEX idx_status (status),
CONSTRAINT fk_br_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT fk_br_category_group FOREIGN KEY (category_group_id) REFERENCES category_groups(id) ON DELETE CASCADE,
CONSTRAINT fk_br_status FOREIGN KEY (status) REFERENCES status_enum(id) ON DELETE SET NULL
) ENGINE = InnoDB;
create table status_update_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
business_registration_id BIGINT UNSIGNED NOT NULL,
previous_status BIGINT UNSIGNED NOT NULL, -- status enum description id
new_status BIGINT UNSIGNED NOT NULL, -- status enum description id
changed_by BIGINT UNSIGNED NOT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_business_registration_id (business_registration_id),
INDEX idx_changed_by (changed_by),
CONSTRAINT fk_sual_business_registration FOREIGN KEY (business_registration_id) REFERENCES business_registration(id) ON DELETE CASCADE,
CONSTRAINT fk_sual_previous_status FOREIGN KEY (previous_status) REFERENCES status_enum(id) ON DELETE RESTRICT,
CONSTRAINT fk_sual_new_status FOREIGN KEY (new_status) REFERENCES status_enum(id) ON DELETE RESTRICT,
CONSTRAINT fk_sual_changed_by FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE = InnoDB;
create table business_documents (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
business_registration_id BIGINT UNSIGNED NOT NULL,
document_type VARCHAR(100) NOT NULL,
document_path VARCHAR(500) NOT NULL,
document_status BIGINT UNSIGNED NOT NULL,-- from status enum description id
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_by BIGINT UNSIGNED DEFAULT NULL,
updated_by BIGINT UNSIGNED DEFAULT NULL,
deleted_by BIGINT UNSIGNED DEFAULT NULL,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_business_registration_id (business_registration_id),
INDEX idx_document_status (document_status),
CONSTRAINT fk_bd_business_registration FOREIGN KEY (business_registration_id) REFERENCES business_registration(id) ON DELETE CASCADE,
CONSTRAINT fk_bd_document_status FOREIGN KEY (document_status) REFERENCES status_enum(id) ON DELETE RESTRICT
) ENGINE = InnoDB;
CREATE TABLE login_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
logout_time TIMESTAMP NULL DEFAULT NULL COMMENT 'NULL = active session',
ip_address VARCHAR(45) NULL,
description JSON NULL COMMENT '{"status":"success","country":"India","countryCode":"IN","region":"MH","regionName":"Maharashtra","city":"Mumbai","zip":"400017","lat":19.074799999999999755573298898525536060333251953125,"lon":72.8855999999999966121322358958423137664794921875,"timezone":"Asia\/Kolkata","isp":"Hathway IP over Cable Internet Access","org":"Hathway Cable and Datacom Limited","as":"AS17488 Hathway IP Over Cable Internet","query":"60.254.0.126","browser_name":"Chrome","os_name":"Windows","browser_language":"en","device_type":"desktop","referrer_host":true,"referrer_path":true}',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_user_id (user_id),
INDEX idx_login_time (login_time),
INDEX idx_ip_address (ip_address),
INDEX idx_user_login_time (user_id, login_time),
INDEX idx_user_active (user_id, logout_time),
CONSTRAINT fk_login_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE activity_logs (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
activity_type VARCHAR(100) NOT NULL,
description TEXT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
INDEX idx_activity_user (user_id),
INDEX idx_activity_type (activity_type),
INDEX idx_activity_user_time (user_id, created_at),
CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE business_addresses (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
business_registration_id BIGINT UNSIGNED NOT NULL,
address_line1 VARCHAR(255) NOT NULL,
address_line2 VARCHAR(255) NULL,
city VARCHAR(100) NOT NULL,
state VARCHAR(100) NOT NULL,
country VARCHAR(100) NOT NULL,
pincode VARCHAR(20) NOT NULL,
latitude DECIMAL(10, 8) NULL,
longitude DECIMAL(11, 8) NULL,
is_active TINYINT(1) NOT NULL DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
INDEX idx_ba_business (business_registration_id),
INDEX idx_ba_business_active (business_registration_id, is_active),
INDEX idx_ba_state_city (state, city),
UNIQUE KEY uk_business_address ( business_registration_id, address_line1, city, state, pincode ),
CONSTRAINT fk_ba_business_registration FOREIGN KEY (business_registration_id) REFERENCES business_registration(id) ON DELETE CASCADE
) ENGINE=InnoDB;
create table roles (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
alias VARCHAR(50) NOT NULL,
description TEXT NULL,
guard_name VARCHAR(50) NOT NULL DEFAULT 'web, api',
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL
) ENGINE = InnoDB;
create table permissions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL UNIQUE,
description TEXT NULL,
guard_name VARCHAR(50) NOT NULL DEFAULT 'web, api',
group_name VARCHAR(50) NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL
) ENGINE = InnoDB;
create table role_has_permissions (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
role_id BIGINT UNSIGNED NOT NULL,
permission_id BIGINT UNSIGNED NOT NULL,
is_active TINYINT DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
UNIQUE KEY uniq_role_permission (role_id, permission_id),
INDEX idx_role_id (role_id),
INDEX idx_permission_id (permission_id),
CONSTRAINT fk_rpm_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
CONSTRAINT fk_rpm_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE = InnoDB;
CREATE TABLE user_permission_overrides (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
permission_id BIGINT UNSIGNED NOT NULL,
type ENUM('additional','limit') NOT NULL COMMENT 'additional=grant extra permission, limit=restrict permission',
is_active TINYINT(1) DEFAULT 1 COMMENT '1=Active, 0=Inactive',
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
UNIQUE KEY uniq_user_permission_type (user_id, permission_id, type, deleted_at),
INDEX idx_user_id (user_id),
INDEX idx_permission_id (permission_id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;