# Database Schema and Project Structure

This document details the SQLite database tables and schema structures for the **Khandan (خاندان)** application.

---

## 1. Directory Blueprint

```
Khandan/
├── .agents/
│   └── skills/
│       └── ftp-deploy/         # FTP deployment skill configuration
├── docs/                       # Project conceptual docs
├── public/                     # Public web assets (accessible online)
│   ├── css/
│   │   └── style.css           # Styling
│   ├── js/
│   │   └── app.js              # Frontend operations
│   ├── uploads/                # Upload folders split by functionality
│   ├── manifest.json           # PWA metadata
│   └── sw.js                   # PWA Service Worker caching shell
└── src/                        # Core Application Source (protected)
    ├── config/
    │   └── config.php          # Database setup configuration
    ├── database/
    │   ├── khandan.db          # Active SQLite Database
    │   └── schema.sql          # DB schema definition
    ├── includes/
    │   ├── auth.php            # Security/Login logic
    │   ├── db.php              # Persistent SQLite PDO instances
    │   └── functions.php       # Globals & escape modules
    ├── views/                  # Dashboard blocks
    └── index.php               # Unified entry-point controller
```

---

## 2. SQLite Database Schema Tables

### Table: `users`
Stores all family members, their status, vital records, and clan authentication rules.
```sql
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE,
    password_hash TEXT,
    full_name TEXT NOT NULL,
    role TEXT DEFAULT 'member',        -- 'patriarch', 'member'
    status TEXT DEFAULT 'pending',     -- 'pending', 'active', 'deceased'
    phone TEXT,
    email TEXT,
    avatar TEXT,
    birthday DATE,
    death_day DATE,
    grave_coords TEXT,                 -- Lat,Lng coordinates for Grave Locator
    invite_code TEXT,                  -- Invitation code created by Patriarch
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```

### Table: `relations`
Maps connections inside the dynamic Family Tree.
```sql
CREATE TABLE relations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL,
    relative_id INTEGER NOT NULL,
    relation_type TEXT NOT NULL,       -- 'father', 'mother', 'spouse', 'child'
    FOREIGN KEY(member_id) REFERENCES users(id),
    FOREIGN KEY(relative_id) REFERENCES users(id)
);
```

### Table: `posts`
Represents the unified private social feed, holding media, recipes, travel diaries, and documents.
```sql
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL,
    category TEXT NOT NULL,            -- 'diary', 'recipe', 'archive', 'travel', 'video'
    title TEXT NOT NULL,
    content TEXT,
    media_url TEXT,                    -- Path to uploaded photo/video/audio
    hashtags TEXT,                     -- String containing space-separated hashtags
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(author_id) REFERENCES users(id)
);
```

### Table: `testaments`
Wills and testaments in audio, video, or text formats.
```sql
CREATE TABLE testaments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL,
    status TEXT DEFAULT 'draft',        -- 'draft', 'active', 'released' (after probate)
    content_text TEXT,
    audio_url TEXT,
    video_url TEXT,
    signature_url TEXT,
    verified_by_patriarch INTEGER DEFAULT 0, -- Boolean (0/1)
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(member_id) REFERENCES users(id)
);
```

### Table: `vault_items`
Digital Safety Deposit Box items containing encrypted passwords, crypto private keys, and files.
```sql
CREATE TABLE vault_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    member_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content_encrypted TEXT,             -- Encrypted credentials/passwords/OTP data
    file_url_encrypted TEXT,            -- Path to encrypted documents
    item_type TEXT NOT NULL,            -- 'credential', 'crypto', 'document'
    access_rules_json TEXT,             -- JSON rules on when and who gets access (e.g. on death, legal heirs)
    release_status INTEGER DEFAULT 0,   -- Boolean (0/1) indicating item is unlocked
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(member_id) REFERENCES users(id)
);
```

### Table: `funds`
Family cooperative savings pools.
```sql
CREATE TABLE funds (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    balance REAL DEFAULT 0.0,
    rules_json TEXT,                    -- Configurations for lending & interest rates
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```

### Table: `fund_transactions`
Ledger of monthly savings deposits, loan distributions, and emergency aids.
```sql
CREATE TABLE fund_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fund_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    type TEXT NOT NULL,                -- 'deposit', 'withdrawal', 'loan_payout', 'loan_repayment', 'emergency_grant'
    note TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(fund_id) REFERENCES funds(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);
```

### Table: `loans`
Active loan queues and distributions.
```sql
CREATE TABLE loans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    fund_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    status TEXT DEFAULT 'requested',    -- 'requested', 'active', 'repaid', 'defaulted'
    duration_months INTEGER NOT NULL,
    requested_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    approved_at DATETIME,
    FOREIGN KEY(fund_id) REFERENCES funds(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);
```

### Table: `arbitrations`
Elder-mediated dispute resolution portal logs.
```sql
CREATE TABLE arbitrations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    complainant_id INTEGER NOT NULL,
    respondent_id INTEGER NOT NULL,
    subject TEXT NOT NULL,
    detail TEXT NOT NULL,
    status TEXT DEFAULT 'pending',     -- 'pending', 'active', 'resolved'
    resolution TEXT,
    arbitrator_id INTEGER,             -- ID of Elder resolving the dispute
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(complainant_id) REFERENCES users(id),
    FOREIGN KEY(respondent_id) REFERENCES users(id),
    FOREIGN KEY(arbitrator_id) REFERENCES users(id)
);
```

### Table: `polls`
Decisional voting configurations for rules and location coordinates.
```sql
CREATE TABLE polls (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    question TEXT NOT NULL,
    options_json TEXT NOT NULL,         -- JSON array of choices
    expires_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
```

### Table: `poll_votes`
Aggregations of formal decisions.
```sql
CREATE TABLE poll_votes (
    poll_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    option_index INTEGER NOT NULL,
    cast_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(poll_id, user_id),
    FOREIGN KEY(poll_id) REFERENCES polls(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);
```

### Table: `internal_proxies`
Digital delegation structures to proxy voting rules to another relative.
```sql
CREATE TABLE internal_proxies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    delegator_id INTEGER NOT NULL,
    proxy_id INTEGER NOT NULL,
    status INTEGER DEFAULT 1,          -- Boolean status indicating active delegation
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(delegator_id) REFERENCES users(id),
    FOREIGN KEY(proxy_id) REFERENCES users(id)
);
```