-- MySQL8 reference schema. Empty database only; not a complete Laravel migration set.
-- All amounts in integer poisha. Ledger balancing/posting/authorization enforced by application.
SET NAMES utf8mb4;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL, email VARCHAR(190) UNIQUE, phone VARCHAR(30) UNIQUE, password VARCHAR(255) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', email_verified_at DATETIME NULL,
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE geographies (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 parent_id BIGINT UNSIGNED NULL, kind VARCHAR(30) NOT NULL, code VARCHAR(80) UNIQUE, name_bn VARCHAR(150) NOT NULL, name_en VARCHAR(150), FOREIGN KEY(parent_id) REFERENCES geographies(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(60) NOT NULL UNIQUE,
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, role_id BIGINT UNSIGNED NOT NULL, geography_id BIGINT UNSIGNED NULL, UNIQUE(user_id,role_id,geography_id), FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(geography_id) REFERENCES geographies(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE partners (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE, geography_id BIGINT UNSIGNED NOT NULL, tier VARCHAR(30) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', referral_code VARCHAR(60) UNIQUE, active_division_scope BIGINT UNSIGNED GENERATED ALWAYS AS (CASE WHEN tier='division' AND status='active' THEN geography_id ELSE NULL END) STORED UNIQUE, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(geography_id) REFERENCES geographies(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE merchants (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL UNIQUE, partner_id BIGINT UNSIGNED NOT NULL, geography_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, category VARCHAR(80), status VARCHAR(30) NOT NULL DEFAULT 'pending', consent_at DATETIME NULL, activated_at DATETIME NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(partner_id) REFERENCES partners(id), FOREIGN KEY(geography_id) REFERENCES geographies(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE delivery_members (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL UNIQUE, partner_id BIGINT UNSIGNED NOT NULL, geography_id BIGINT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(partner_id) REFERENCES partners(id), FOREIGN KEY(geography_id) REFERENCES geographies(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE products (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id BIGINT UNSIGNED NOT NULL, name VARCHAR(190) NOT NULL, price_poisha BIGINT NOT NULL, stock INT NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'draft', FOREIGN KEY(merchant_id) REFERENCES merchants(id), CHECK(price_poisha>=0), CHECK(stock>=0),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE orders (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id BIGINT UNSIGNED NOT NULL, partner_id BIGINT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', gross_poisha BIGINT NOT NULL DEFAULT 0, earned_company_revenue_poisha BIGINT NOT NULL DEFAULT 0, completed_at DATETIME NULL, FOREIGN KEY(merchant_id) REFERENCES merchants(id), FOREIGN KEY(partner_id) REFERENCES partners(id), CHECK(gross_poisha>=0), CHECK(earned_company_revenue_poisha>=0),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, quantity INT NOT NULL, unit_price_poisha BIGINT NOT NULL, FOREIGN KEY(order_id) REFERENCES orders(id), FOREIGN KEY(product_id) REFERENCES products(id), CHECK(quantity>0), CHECK(unit_price_poisha>=0),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 order_id BIGINT UNSIGNED NOT NULL, delivery_member_id BIGINT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'assigned', compensation_poisha BIGINT NOT NULL DEFAULT 0, evidence_path VARCHAR(255), paid_reference VARCHAR(190), FOREIGN KEY(order_id) REFERENCES orders(id), FOREIGN KEY(delivery_member_id) REFERENCES delivery_members(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE bookings (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id BIGINT UNSIGNED NOT NULL, partner_id BIGINT UNSIGNED NOT NULL, service_category VARCHAR(100) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', scheduled_at DATETIME NULL, earned_company_revenue_poisha BIGINT NOT NULL DEFAULT 0, FOREIGN KEY(merchant_id) REFERENCES merchants(id), FOREIGN KEY(partner_id) REFERENCES partners(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE work_tasks (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 partner_id BIGINT UNSIGNED NOT NULL, merchant_id BIGINT UNSIGNED NULL, title VARCHAR(190) NOT NULL, type VARCHAR(80) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'planned', due_at DATETIME NULL, evidence_path VARCHAR(255), verified_by BIGINT UNSIGNED NULL, FOREIGN KEY(partner_id) REFERENCES partners(id), FOREIGN KEY(merchant_id) REFERENCES merchants(id), FOREIGN KEY(verified_by) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commission_rules (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 code VARCHAR(80) NOT NULL, version INT NOT NULL, fixed_poisha BIGINT NOT NULL DEFAULT 0, basis_points INT NOT NULL DEFAULT 0, enabled BOOLEAN NOT NULL DEFAULT FALSE, UNIQUE(code,version), CHECK(basis_points BETWEEN 0 AND 10000),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ledger_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NULL, code VARCHAR(100) NOT NULL UNIQUE, pocket VARCHAR(30) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'BDT', FOREIGN KEY(user_id) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ledger_journals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 idempotency_key VARCHAR(190) NOT NULL UNIQUE, type VARCHAR(60) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', reversal_of BIGINT UNSIGNED NULL, posted_at DATETIME NULL, approved_by BIGINT UNSIGNED NULL, FOREIGN KEY(reversal_of) REFERENCES ledger_journals(id), FOREIGN KEY(approved_by) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ledger_entries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 journal_id BIGINT UNSIGNED NOT NULL, account_id BIGINT UNSIGNED NOT NULL, side CHAR(1) NOT NULL, amount_poisha BIGINT NOT NULL, FOREIGN KEY(journal_id) REFERENCES ledger_journals(id), FOREIGN KEY(account_id) REFERENCES ledger_accounts(id), CHECK(side IN ('D','C')), CHECK(amount_poisha>0), INDEX(account_id,journal_id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 partner_id BIGINT UNSIGNED NOT NULL, rule_id BIGINT UNSIGNED NOT NULL, source_event VARCHAR(190) NOT NULL, eligible_base_poisha BIGINT NOT NULL DEFAULT 0, amount_poisha BIGINT NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', journal_id BIGINT UNSIGNED NULL, UNIQUE(partner_id,rule_id,source_event), FOREIGN KEY(partner_id) REFERENCES partners(id), FOREIGN KEY(rule_id) REFERENCES commission_rules(id), FOREIGN KEY(journal_id) REFERENCES ledger_journals(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE wallet_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, source_account_id BIGINT UNSIGNED NOT NULL, type VARCHAR(40) NOT NULL, amount_poisha BIGINT NOT NULL, fee_poisha BIGINT NOT NULL DEFAULT 0, status VARCHAR(30) NOT NULL DEFAULT 'requested', idempotency_key VARCHAR(190) NOT NULL UNIQUE, destination_ciphertext TEXT NULL, provider_reference VARCHAR(190) UNIQUE, reservation_journal_id BIGINT UNSIGNED NULL, settlement_journal_id BIGINT UNSIGNED NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(source_account_id) REFERENCES ledger_accounts(id), FOREIGN KEY(reservation_journal_id) REFERENCES ledger_journals(id), FOREIGN KEY(settlement_journal_id) REFERENCES ledger_journals(id), CHECK(amount_poisha>0), CHECK(fee_poisha>=0),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE provider_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 provider VARCHAR(80) NOT NULL, event_id VARCHAR(190) NOT NULL, transaction_id VARCHAR(190), verified BOOLEAN NOT NULL DEFAULT FALSE, processed_at DATETIME NULL, payload_hash CHAR(64) NOT NULL, UNIQUE(provider,event_id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE referrals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 inviter_partner_id BIGINT UNSIGNED NOT NULL, invited_partner_id BIGINT UNSIGNED NOT NULL UNIQUE, status VARCHAR(30) NOT NULL DEFAULT 'pending', qualifying_event VARCHAR(190) UNIQUE, commission_id BIGINT UNSIGNED NULL, FOREIGN KEY(inviter_partner_id) REFERENCES partners(id), FOREIGN KEY(invited_partner_id) REFERENCES partners(id), FOREIGN KEY(commission_id) REFERENCES commissions(id), CHECK(inviter_partner_id<>invited_partner_id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE activation_reviews (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 partner_id BIGINT UNSIGNED NOT NULL, step VARCHAR(60) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'pending', reviewer_id BIGINT UNSIGNED NULL, reviewed_at DATETIME NULL, UNIQUE(partner_id,step), FOREIGN KEY(partner_id) REFERENCES partners(id), FOREIGN KEY(reviewer_id) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE loan_applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 merchant_id BIGINT UNSIGNED NOT NULL, partner_id BIGINT UNSIGNED NOT NULL, requested_poisha BIGINT NOT NULL, purpose TEXT NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'draft', consent_at DATETIME NULL, lender_reference VARCHAR(190), FOREIGN KEY(merchant_id) REFERENCES merchants(id), FOREIGN KEY(partner_id) REFERENCES partners(id), CHECK(requested_poisha>0),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE courses (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(190) NOT NULL, description TEXT, status VARCHAR(30) NOT NULL DEFAULT 'draft',
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE lessons (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 course_id BIGINT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL, content TEXT, position INT NOT NULL, FOREIGN KEY(course_id) REFERENCES courses(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE enrollments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, course_id BIGINT UNSIGNED NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'enrolled', completed_at DATETIME NULL, UNIQUE(user_id,course_id), FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(course_id) REFERENCES courses(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_tickets (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL, subject VARCHAR(190) NOT NULL, category VARCHAR(80) NOT NULL, status VARCHAR(30) NOT NULL DEFAULT 'open', assigned_to BIGINT UNSIGNED NULL, FOREIGN KEY(user_id) REFERENCES users(id), FOREIGN KEY(assigned_to) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 ticket_id BIGINT UNSIGNED NOT NULL, author_id BIGINT UNSIGNED NOT NULL, message TEXT NOT NULL, FOREIGN KEY(ticket_id) REFERENCES support_tickets(id), FOREIGN KEY(author_id) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 actor_id BIGINT UNSIGNED NULL, action VARCHAR(100) NOT NULL, entity_type VARCHAR(100) NOT NULL, entity_id BIGINT UNSIGNED, redacted_changes JSON, occurred_at DATETIME NOT NULL, FOREIGN KEY(actor_id) REFERENCES users(id),
 created_at TIMESTAMP NULL, updated_at TIMESTAMP NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;