-- Money Manager restore (recreate tables)
USE erp1;

CREATE TABLE IF NOT EXISTS money_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    membership_no VARCHAR(50) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NOT NULL,
    full_name VARCHAR(200) NOT NULL,
    id_type VARCHAR(30) NOT NULL,
    id_number VARCHAR(100) NOT NULL,
    id_issue_date DATE NULL,
    id_expiry_date DATE NOT NULL,
    date_of_birth DATE NULL,
    nationality VARCHAR(100) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    phone_alt VARCHAR(50) NULL,
    address_line1 VARCHAR(150) NULL,
    address_line2 VARCHAR(150) NULL,
    city VARCHAR(100) NULL,
    state VARCHAR(100) NULL,
    postal_code VARCHAR(20) NULL,
    country VARCHAR(100) NULL,
    kyc_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    kyc_verified_at DATETIME NULL,
    kyc_verified_by BIGINT UNSIGNED NULL,
    pep_flag TINYINT NOT NULL DEFAULT 0,
    aml_checked TINYINT NOT NULL DEFAULT 0,
    sanction_screened TINYINT NOT NULL DEFAULT 0,
    risk_level VARCHAR(20) NULL,
    risk_tags TEXT NULL,
    compliance_notes TEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    onboarding_date DATE NULL,
    relationship_manager VARCHAR(150) NULL,
    segment VARCHAR(50) NULL,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_money_customer_membership (company_id, membership_no),
    UNIQUE KEY uq_money_customer_idnum (company_id, id_number),
    CONSTRAINT fk_money_customer_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_customer_kyc_by FOREIGN KEY (kyc_verified_by) REFERENCES users(id),
    CONSTRAINT fk_money_customer_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_money_customer_updated_by FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    owner_user_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    account_no VARCHAR(100) NULL,
    iban VARCHAR(50) NULL,
    account_type VARCHAR(30) NOT NULL,
    account_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    opened_on DATE NULL,
    branch_name VARCHAR(100) NULL,
    currency_code CHAR(3) NOT NULL,
    opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    current_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
    credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0,
    visibility ENUM('private', 'shared', 'public') NOT NULL DEFAULT 'private',
    public_access ENUM('view', 'operate') NOT NULL DEFAULT 'view',
    notes VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_money_account_no (company_id, account_no),
    UNIQUE KEY uq_money_iban (company_id, iban),
    CONSTRAINT fk_money_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_customer FOREIGN KEY (customer_id) REFERENCES money_customers(id),
    CONSTRAINT fk_money_owner FOREIGN KEY (owner_user_id) REFERENCES users(id),
    CONSTRAINT fk_money_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_account_permissions (
    account_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    can_view TINYINT NOT NULL DEFAULT 1,
    can_operate TINYINT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (account_id, user_id),
    CONSTRAINT fk_mperm_account FOREIGN KEY (account_id) REFERENCES money_accounts(id),
    CONSTRAINT fk_mperm_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_transactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    type ENUM('debit', 'credit', 'transfer_in', 'transfer_out') NOT NULL,
    amount DECIMAL(18,2) NOT NULL,
    currency_code CHAR(3) NOT NULL,
    transaction_date DATE NOT NULL,
    description VARCHAR(255) NULL,
    reference VARCHAR(100) NULL,
    related_account_id BIGINT UNSIGNED NULL,
    transfer_id VARCHAR(64) NULL,
    admin_note TEXT NULL,
    admin_tags VARCHAR(255) NULL,
    admin_status VARCHAR(30) NULL DEFAULT 'approved',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_mtxn_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_mtxn_account FOREIGN KEY (account_id) REFERENCES money_accounts(id),
    CONSTRAINT fk_mtxn_user FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT fk_mtxn_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS money_settings (
    company_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    account_no_prefix VARCHAR(30) NOT NULL DEFAULT 'ACC',
    iban_prefix VARCHAR(30) NOT NULL DEFAULT 'IBAN',
    money_logo_path VARCHAR(255) NULL,
    print_header_title VARCHAR(150) NULL,
    print_header_line1 VARCHAR(150) NULL,
    print_header_line2 VARCHAR(150) NULL,
    print_header_line3 VARCHAR(150) NULL,
    print_header_footer VARCHAR(150) NULL,
    updated_by BIGINT UNSIGNED NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_money_settings_company FOREIGN KEY (company_id) REFERENCES companies(id),
    CONSTRAINT fk_money_settings_user FOREIGN KEY (updated_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
