-- =====================================================================
-- SMART DUKAAN - COMPLETE SAAS DATABASE SCHEMA
-- Compatible with MySQL 8.0+ / MariaDB 10.4+ / phpMyAdmin / cPanel / Hostinger
-- Complete Multi-Tenant Architecture with shop_id Isolation,
-- RBAC, Subscriptions, Coupons, Support, Audit Logs, and Ads
-- =====================================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS personal_access_tokens;
DROP TABLE IF EXISTS support_replies;
DROP TABLE IF EXISTS support_tickets;
DROP TABLE IF EXISTS ads;
DROP TABLE IF EXISTS coupons;
DROP TABLE IF EXISTS referrals;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS subscription_transactions;
DROP TABLE IF EXISTS subscriptions;
DROP TABLE IF EXISTS plan_features;
DROP TABLE IF EXISTS subscription_plans;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS daily_closings;
DROP TABLE IF EXISTS expenses;
DROP TABLE IF EXISTS stock_movements;
DROP TABLE IF EXISTS purchase_items;
DROP TABLE IF EXISTS purchases;
DROP TABLE IF EXISTS supplier_transactions;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS customer_transactions;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS payment_transactions;
DROP TABLE IF EXISTS sale_items;
DROP TABLE IF EXISTS sales;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS shops;
DROP TABLE IF EXISTS users;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Users Table (Super Admin, Admins, Store Owners, Staff)
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) UNIQUE,
    mobile VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('SUPER_ADMIN', 'ADMIN', 'SUPPORT', 'OWNER', 'MANAGER', 'CASHIER', 'STAFF') DEFAULT 'OWNER',
    is_active TINYINT(1) DEFAULT 1,
    remember_token VARCHAR(100) NULL,
    email_verified_at TIMESTAMP NULL,
    mobile_verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_mobile (mobile),
    INDEX idx_user_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 1b. Personal Access Tokens (Laravel Sanctum Authentication)
CREATE TABLE personal_access_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tokenable_type VARCHAR(255) NOT NULL,
    tokenable_id VARCHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    abilities TEXT NULL,
    last_used_at TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX personal_access_tokens_tokenable_type_tokenable_id_index (tokenable_type, tokenable_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Shops Table (SaaS Tenant Container)
CREATE TABLE shops (
    id VARCHAR(36) PRIMARY KEY,
    owner_id VARCHAR(36) NOT NULL,
    name VARCHAR(150) NOT NULL,
    business_type VARCHAR(60) DEFAULT 'GROCERY',
    owner_name VARCHAR(120),
    mobile VARCHAR(20),
    email VARCHAR(150),
    address TEXT,
    city VARCHAR(80),
    state VARCHAR(80),
    pincode VARCHAR(10),
    gstin VARCHAR(30),
    logo VARCHAR(255),
    upi_id VARCHAR(100) DEFAULT 'dukaan@upi',
    invoice_prefix VARCHAR(10) DEFAULT 'INV',
    currency VARCHAR(10) DEFAULT 'INR',
    language VARCHAR(10) DEFAULT 'en',
    current_plan VARCHAR(30) DEFAULT 'PRO',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_shop_owner (owner_id),
    INDEX idx_shop_status (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. RBAC Roles & Permissions
CREATE TABLE roles (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE permissions (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE,
    display_name VARCHAR(120) NOT NULL,
    module VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_permissions (
    role_id VARCHAR(36) NOT NULL,
    permission_id VARCHAR(36) NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
    user_id VARCHAR(36) NOT NULL,
    role_id VARCHAR(36) NOT NULL,
    shop_id VARCHAR(36) NULL,
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Product Categories
CREATE TABLE categories (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    name VARCHAR(100) NOT NULL,
    hindi_name VARCHAR(100) NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_cat_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Products Catalog
CREATE TABLE products (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    category_id VARCHAR(36) NULL,
    name VARCHAR(180) NOT NULL,
    hindi_name VARCHAR(180) NULL,
    unit VARCHAR(20) DEFAULT 'KG',
    purchase_price DECIMAL(15,2) DEFAULT 0.00,
    selling_price DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    wholesale_price DECIMAL(15,2) DEFAULT 0.00,
    current_stock DECIMAL(15,3) DEFAULT 0.000,
    min_stock_alert DECIMAL(15,3) DEFAULT 5.000,
    barcode VARCHAR(100) NULL,
    sku VARCHAR(100) NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_prod_shop (shop_id),
    INDEX idx_prod_barcode (shop_id, barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Customers Table
CREATE TABLE customers (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    name VARCHAR(120) NOT NULL,
    mobile VARCHAR(20) NULL,
    email VARCHAR(120) NULL,
    address TEXT NULL,
    balance_due DECIMAL(15,2) DEFAULT 0.00,
    credit_limit DECIMAL(15,2) DEFAULT 50000.00,
    customer_type VARCHAR(30) DEFAULT 'RETAIL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_cust_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Customer Transactions (Khata Ledger with Bill Photo Attachments)
CREATE TABLE customer_transactions (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    customer_id VARCHAR(36) NOT NULL,
    type ENUM('CREDIT_GIVEN', 'PAYMENT_RECEIVED', 'ADJUSTMENT') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    reference_invoice_no VARCHAR(50) NULL,
    note TEXT NULL,
    payment_method VARCHAR(30) DEFAULT 'CASH',
    photo_url VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_ctx_shop (shop_id, customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Sales Master (POS Billing)
CREATE TABLE sales (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    invoice_no VARCHAR(50) NOT NULL,
    customer_id VARCHAR(36) NULL,
    customer_name VARCHAR(120) DEFAULT 'Walk-in Customer',
    customer_mobile VARCHAR(20) NULL,
    subtotal DECIMAL(15,2) NOT NULL,
    discount DECIMAL(15,2) DEFAULT 0.00,
    tax DECIMAL(15,2) DEFAULT 0.00,
    grand_total DECIMAL(15,2) NOT NULL,
    paid_amount DECIMAL(15,2) NOT NULL,
    credit_amount DECIMAL(15,2) DEFAULT 0.00,
    payment_method ENUM('CASH', 'UPI', 'CREDIT', 'CARD', 'SPLIT') DEFAULT 'CASH',
    payment_status ENUM('PAID', 'PENDING', 'FAILED') DEFAULT 'PAID',
    transaction_ref VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    UNIQUE KEY uq_shop_invoice (shop_id, invoice_no),
    INDEX idx_sale_created (shop_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Sale Line Items
CREATE TABLE sale_items (
    id VARCHAR(36) PRIMARY KEY,
    sale_id VARCHAR(36) NOT NULL,
    product_id VARCHAR(36) NOT NULL,
    product_name VARCHAR(180) NOT NULL,
    unit VARCHAR(20) DEFAULT 'KG',
    unit_price DECIMAL(15,2) NOT NULL,
    quantity DECIMAL(15,3) NOT NULL,
    line_total DECIMAL(15,2) NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    INDEX idx_item_sale (sale_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Payment Transactions (Authoritative Idempotent Audit)
CREATE TABLE payment_transactions (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    sale_id VARCHAR(36) NULL,
    payment_method VARCHAR(30) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    status ENUM('CREATED', 'PENDING', 'SUCCESS', 'FAILED', 'MANUAL') DEFAULT 'SUCCESS',
    transaction_reference VARCHAR(100) NULL,
    idempotency_key VARCHAR(100) UNIQUE,
    verified_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_ptx_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Suppliers Table
CREATE TABLE suppliers (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    name VARCHAR(120) NOT NULL,
    mobile VARCHAR(20) NULL,
    company_name VARCHAR(150) NULL,
    balance_due DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Supplier Transactions (Purchase & Payments)
CREATE TABLE supplier_transactions (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    supplier_id VARCHAR(36) NOT NULL,
    type ENUM('PURCHASE', 'PAYMENT_MADE', 'ADJUSTMENT') NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Purchases Table
CREATE TABLE purchases (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    supplier_id VARCHAR(36) NOT NULL,
    supplier_name VARCHAR(120) NOT NULL,
    invoice_no VARCHAR(50) NOT NULL,
    grand_total DECIMAL(15,2) NOT NULL,
    paid_amount DECIMAL(15,2) NOT NULL,
    due_amount DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Stock Movements (Audit Trail of all inventory changes)
CREATE TABLE stock_movements (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    product_id VARCHAR(36) NOT NULL,
    product_name VARCHAR(180) NOT NULL,
    type ENUM('PURCHASE', 'SALE', 'SALE_RETURN', 'DAMAGE', 'ADJUSTMENT', 'OPENING') NOT NULL,
    quantity_change DECIMAL(15,3) NOT NULL,
    stock_after DECIMAL(15,3) NOT NULL,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_sm_shop_prod (shop_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Store Expenses
CREATE TABLE expenses (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    category VARCHAR(80) NOT NULL,
    amount DECIMAL(15,2) NOT NULL,
    payment_method VARCHAR(30) DEFAULT 'CASH',
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_exp_shop (shop_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Daily Closings (End of Day Cash Reconciliations)
CREATE TABLE daily_closings (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    closing_date DATE NOT NULL,
    opening_cash DECIMAL(15,2) DEFAULT 0.00,
    cash_sales DECIMAL(15,2) DEFAULT 0.00,
    upi_sales DECIMAL(15,2) DEFAULT 0.00,
    credit_sales DECIMAL(15,2) DEFAULT 0.00,
    expected_closing_cash DECIMAL(15,2) DEFAULT 0.00,
    actual_closing_cash DECIMAL(15,2) DEFAULT 0.00,
    discrepancy DECIMAL(15,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    INDEX idx_dc_shop (shop_id, closing_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 17. SaaS Subscription Plans & Feature Limits
CREATE TABLE subscription_plans (
    id VARCHAR(36) PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    duration_days INT NOT NULL DEFAULT 30,
    max_products INT NOT NULL DEFAULT 100,
    max_bills_monthly INT NOT NULL DEFAULT 500,
    has_photo_attachment TINYINT(1) DEFAULT 0,
    has_dynamic_upi TINYINT(1) DEFAULT 1,
    has_thermal_print TINYINT(1) DEFAULT 0,
    is_ad_free TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscriptions (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    plan_id VARCHAR(36) NOT NULL,
    status ENUM('TRIAL', 'ACTIVE', 'EXPIRED', 'CANCELLED') DEFAULT 'ACTIVE',
    starts_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id) ON DELETE CASCADE,
    INDEX idx_sub_shop (shop_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 18. Coupons System
CREATE TABLE coupons (
    id VARCHAR(36) PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    discount_type ENUM('PERCENTAGE', 'FIXED') DEFAULT 'PERCENTAGE',
    discount_value DECIMAL(10,2) NOT NULL,
    max_discount DECIMAL(10,2) NULL,
    valid_until TIMESTAMP NULL,
    usage_limit INT DEFAULT 100,
    used_count INT DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 19. Support Tickets System
CREATE TABLE support_tickets (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NOT NULL,
    user_id VARCHAR(36) NOT NULL,
    subject VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    priority ENUM('LOW', 'MEDIUM', 'HIGH') DEFAULT 'MEDIUM',
    status ENUM('OPEN', 'IN_PROGRESS', 'RESOLVED', 'CLOSED') DEFAULT 'OPEN',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 20. Audit Logs System
CREATE TABLE audit_logs (
    id VARCHAR(36) PRIMARY KEY,
    shop_id VARCHAR(36) NULL,
    user_id VARCHAR(36) NULL,
    action VARCHAR(100) NOT NULL,
    target_table VARCHAR(60) NOT NULL,
    target_id VARCHAR(36) NULL,
    old_data JSON NULL,
    new_data JSON NULL,
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_shop (shop_id),
    INDEX idx_audit_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- INITIAL SEED DATA
-- =====================================================================
INSERT INTO subscription_plans (id, name, price, duration_days, max_products, max_bills_monthly, has_photo_attachment, has_dynamic_upi, has_thermal_print, is_ad_free, is_active) VALUES
('plan_free', 'Free Starter', 0.00, 3650, 50, 100, 0, 1, 0, 0, 1),
('plan_pro', 'Pro Dukaan', 99.00, 30, 10000, 100000, 1, 1, 0, 1, 1),
('plan_business', 'Business Multi', 199.00, 30, 50000, 500000, 1, 1, 1, 1, 1);

INSERT INTO users (id, name, email, mobile, password, role) VALUES
('usr_super_admin', 'Smart Dukaan Super Admin', 'admin@smartdukaan.in', '9999999999', '$2y$10$wT8Kj6Y9qH5A2kP7D7fUveX1h2h3h4h5h6h7h8h9h0h1h2h3h4h5', 'SUPER_ADMIN'),
('usr_demo_owner', 'Ramesh Sharma', 'ramesh@sharmastore.com', '9876543210', '$2y$10$wT8Kj6Y9qH5A2kP7D7fUveX1h2h3h4h5h6h7h8h9h0h1h2h3h4h5', 'OWNER');

INSERT INTO shops (id, owner_id, name, business_type, owner_name, mobile, address, upi_id, current_plan) VALUES
('shop_default_01', 'usr_demo_owner', 'Sharma Kirana & General Store', 'GROCERY', 'Ramesh Sharma', '9876543210', 'Main Market Road, City Center', 'sharmakirana@upi', 'PRO');

INSERT INTO subscriptions (id, shop_id, plan_id, status, starts_at, expires_at) VALUES
('sub_01', 'shop_default_01', 'plan_pro', 'ACTIVE', NOW(), DATE_ADD(NOW(), INTERVAL 30 DAY));