-- Credit Card Management Module schema for Famlynk
-- MySQL 8+, InnoDB, utf8mb4

CREATE TABLE IF NOT EXISTS credit_card_payments (
  payment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'manual',
  reference_id VARCHAR(120) NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'posted',
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cc_payments_card_date (card_id, payment_date),
  KEY idx_cc_payments_family_status (family_id, status),
  CONSTRAINT fk_cc_payments_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_payments_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_card_statements (
  statement_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  statement_month INT NOT NULL,
  statement_year INT NOT NULL,
  file_asset_id BIGINT NULL,
  storage_path VARCHAR(255) NULL,
  file_name VARCHAR(255) NULL,
  file_type VARCHAR(20) NULL,
  opening_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_purchases DECIMAL(14,2) NOT NULL DEFAULT 0,
  total_payments DECIMAL(14,2) NOT NULL DEFAULT 0,
  interest_charged DECIMAL(14,2) NOT NULL DEFAULT 0,
  closing_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  minimum_due DECIMAL(14,2) NOT NULL DEFAULT 0,
  due_date DATE NULL,
  parse_status VARCHAR(30) NOT NULL DEFAULT 'uploaded',
  approval_status VARCHAR(30) NOT NULL DEFAULT 'pending',
  approved_by BIGINT NULL,
  approved_at DATETIME NULL,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cc_statement_period (card_id, statement_month, statement_year),
  KEY idx_cc_statements_family_status (family_id, approval_status),
  KEY idx_cc_statements_card_period (card_id, statement_year, statement_month),
  CONSTRAINT fk_cc_statements_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_statements_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_card_statement_transactions (
  statement_txn_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  statement_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  txn_date DATE NOT NULL,
  merchant VARCHAR(255) NOT NULL,
  description VARCHAR(255) NULL,
  txn_type VARCHAR(40) NOT NULL DEFAULT 'charge',
  category VARCHAR(80) NOT NULL DEFAULT 'shopping',
  member_id BIGINT NULL,
  amount DECIMAL(14,2) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  duplicate_key VARCHAR(255) NULL,
  ledger_reference_id BIGINT NULL,
  approved_by BIGINT NULL,
  approved_at DATETIME NULL,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cc_stmt_txn_statement_status (statement_id, status),
  KEY idx_cc_stmt_txn_card_date (card_id, txn_date),
  KEY idx_cc_stmt_txn_duplicate (card_id, duplicate_key),
  CONSTRAINT fk_cc_stmt_txn_statement FOREIGN KEY (statement_id) REFERENCES credit_card_statements(statement_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_stmt_txn_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_stmt_txn_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_card_rewards (
  reward_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  reward_points DECIMAL(14,2) NOT NULL DEFAULT 0,
  cashback_balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  reward_value_estimate DECIMAL(14,2) NOT NULL DEFAULT 0,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_cc_rewards_card (card_id),
  KEY idx_cc_rewards_family (family_id),
  CONSTRAINT fk_cc_rewards_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_rewards_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_card_subscriptions (
  subscription_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  merchant VARCHAR(160) NOT NULL,
  category VARCHAR(80) NOT NULL DEFAULT 'subscriptions',
  amount DECIMAL(14,2) NOT NULL,
  frequency VARCHAR(30) NOT NULL DEFAULT 'monthly',
  last_charge_date DATE NULL,
  next_expected_date DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cc_subscriptions_card_status (card_id, status),
  KEY idx_cc_subscriptions_family (family_id),
  CONSTRAINT fk_cc_subscriptions_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_subscriptions_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS credit_card_activity (
  activity_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  family_id BIGINT NOT NULL,
  card_id BIGINT NOT NULL,
  activity_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  activity_type VARCHAR(60) NOT NULL,
  title VARCHAR(180) NOT NULL,
  details_json TEXT NULL,
  created_by BIGINT NULL,
  updated_by BIGINT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_cc_activity_card_date (card_id, activity_date),
  KEY idx_cc_activity_family_date (family_id, activity_date),
  CONSTRAINT fk_cc_activity_card FOREIGN KEY (card_id) REFERENCES credit_cards(card_id) ON DELETE CASCADE,
  CONSTRAINT fk_cc_activity_family FOREIGN KEY (family_id) REFERENCES families(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sample seed data (assumes family_id=1 and card_id=1 exist)
INSERT INTO credit_card_rewards (family_id, card_id, reward_points, cashback_balance, reward_value_estimate, created_by, updated_by)
SELECT 1, 1, 1220, 245.50, 367.50, 1, 1
WHERE NOT EXISTS (SELECT 1 FROM credit_card_rewards WHERE card_id = 1);

INSERT INTO credit_card_activity (family_id, card_id, activity_type, title, details_json, created_by, updated_by)
SELECT 1, 1, 'CARD_ADDED', 'Card Added', '{"seed":true}', 1, 1
WHERE NOT EXISTS (SELECT 1 FROM credit_card_activity WHERE card_id = 1 AND activity_type = 'CARD_ADDED');